CREATE TYPE ColumnList AS TABLE
(                     
    name SYSNAME NOT NULL PRIMARY KEY
)
GO
 
-- Provided "as is" with no warranties of any kind. User assumes all risks of use.
CREATE PROCEDURE DynamicUnpivot
    @Query NVARCHAR(MAX),                   -- Query producing the resultset to unpivot.
    @Column SYSNAME = NULL,                 -- Name of single column. Combined with @ColumnList to control which columns are unpivoted.
    @ColumnList ColumnList READONLY,        -- List of column names. Combined with @Column to control which columns are unpivoted.
    @UnpivotSpecified BIT = 0,              -- Indicates whether the specified columns (@UnpivotSpecified = 1) or all other columns execpt 
                                            --  the specified (@UnpivotSpecified = 0) should be unpivoted.
    @ValueColumnName SYSNAME = 'Value',     -- Name of value-containing column in the output resultset.
    @ColumnColumnName SYSNAME = 'Column',   -- Name of column-name-containing column in the output resultset.
    @ValueColumnType NVARCHAR(20) = 'NVARCHAR(100)',    -- Data type of output value column. All source columns that are unpivoted are CAST to this data type.
    @OnlyOutputSql BIT = 0                  -- Controls whether the unpivot query is executed and its resultset returned (@OnlyOutputSql = 0) 
                                            --  or not executed and its SQL outputted (@OnlyOutputSql = 1).
AS
BEGIN
SET NOCOUNT ON;
 
 
/*
    Combine both column name inputs into one table variable. 
    UNIONing protects from duplicates when @Column is also in @ColumnList
*/
DECLARE @Columns dbo.ColumnList
 
INSERT INTO @Columns
SELECT name FROM @ColumnList
UNION
SELECT @Column WHERE @Column IS NOT NULL
 
 
/*
    Build a list of all columns to be returned, indicating which should be unpivoted.
 
    If @UnpivotSpecified = 1, column names found in *both* the input column names and the
    query's result set will be unpivoted. All other columns in the result set will be 
    output without unpivoting. 
     
    @UnpivotSpecified = 0 inverts this behavior.
*/
DECLARE @QueryColumns TABLE (
    name sysname NOT NULL PRIMARY KEY, 
    column_ordinal INT NOT NULL,
    ShouldUnpivot BIT NOT NULL
);
 
INSERT @QueryColumns
SELECT
    r.name, 
    column_ordinal, 
    ShouldUnpivot = CASE WHEN @UnpivotSpecified = 1 THEN
        CASE WHEN c.name IS NOT NULL THEN 1 ELSE 0 END
    ELSE
        CASE WHEN c.name IS NOT NULL THEN 0 ELSE 1 END
    END
FROM sys.dm_exec_describe_first_result_set(@Query, NULL, 0) r
    LEFT JOIN @Columns c ON r.name = c.name
WHERE is_hidden = 0
    AND r.name IS NOT NULL
    AND column_ordinal IS NOT NULL
 
 
DECLARE @ColumnName SYSNAME,
    @First BIT = 1
 
/*
    Assemble cast & unpivot SQL.
*/
DECLARE @CastSql NVARCHAR(MAX) = '',
    @UnpivotSql NVARCHAR(MAX) = ''
 
 
DECLARE upvt CURSOR FAST_FORWARD FOR 
    SELECT name FROM @QueryColumns WHERE ShouldUnpivot = 1 ORDER BY column_ordinal
 
OPEN upvt
 
FETCH NEXT FROM upvt 
    INTO @ColumnName;
 
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @First = 0 
    BEGIN
        SET @CastSql += ', '
        SET @UnpivotSql += ', '
    END
 
    SET @CastSql += QUOTENAME(@ColumnName) + ' = CAST(' + QUOTENAME(@ColumnName) + ' AS ' + @ValueColumnType + ')'
    SET @UnpivotSql += QUOTENAME(@ColumnName)
 
    IF @First = 1
        SET @First = 0
 
    FETCH NEXT FROM upvt
        INTO @ColumnName
END
 
CLOSE upvt
DEALLOCATE upvt
 
 
/*
    Assemble SQL for non-unpivoted columns.
*/
DECLARE @NoUnpivotSql NVARCHAR(MAX) = ''
SET @First = 1
 
DECLARE npvt CURSOR FAST_FORWARD FOR 
    SELECT name FROM @QueryColumns WHERE ShouldUnpivot = 0 ORDER BY column_ordinal
 
OPEN npvt
 
FETCH NEXT FROM npvt
    INTO @ColumnName
 
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @First = 0 
        SET @NoUnpivotSql += ', '
         
    SET @NoUnpivotSql += QUOTENAME(@ColumnName)
 
    IF @First = 1
        SET @First = 0
 
    FETCH NEXT FROM npvt
        INTO @ColumnName
END;
 
CLOSE npvt
DEALLOCATE npvt
 
 
/*
    Build the final query.
*/
DECLARE @NewLine NVARCHAR(2) = CHAR(13) + CHAR(10),
    @Tab NVARCHAR(1) = CHAR(9)
DECLARE @OutputSql NVARCHAR(max) = 'SELECT *
FROM (
    SELECT
        ' + @NoUnpivotSql + ', 
        ' + @CastSql + '
    FROM (
        ' + @Query + '
        ) innerData
) data
    UNPIVOT (' + QUOTENAME(@ValueColumnName) + ' FOR ' + QUOTENAME(@ColumnColumnName) + ' IN (' + @UnpivotSql + ')) upvt'
 
/*
    Produce output.
*/
SET NOCOUNT OFF
 
IF @OnlyOutputSql = 0
    EXEC sp_executesql @OutputSql
ELSE
    PRINT @OutputSql
 
END
GO

If(OBJECT_ID('tempdb..#TempT2') Is Not Null)
Begin  Drop Table #TempT2 End
SELECT * into #TempT2
 FROM (VALUES
	 (N'т1',N'col2_1',N'col3_1',6,6,3,2,6,5,8)
	,(N'т2',N'col2_2',N'col3_2',0,0,0,0,0,0,0)
	,(N'т3',N'col2_3',N'col3_3',0,0,0,0,0,0,0)
	,(N'т4',N'col2_4',N'col3_4',14,13,8,5,14,16,12)
	,(N'т5',N'col2_5',N'col3_5',4,4,2,1,5,3,4)
	,(N'т6',N'col2_6',N'col3_6',0,1,1,1,1,2,1)
	,(N'т7',N'col2_7',N'col3_7',5,4,4,2,3,5,4)
) as x([col1],[col2],[col3],[col4],[col5],[col6],[col7],[col8],[col9],[col10])
Select * from #TempT2