Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Динамический Unpivot с несколькими столбцами  [new]
Yaroslav85
Member

Откуда:
Сообщений: 17
Всем привет.
На просторах интернета нашел интересную процедуру для динамического Unpivot:
тут:
https://bengribaudo.com/blog/2015/02/05/3383/dynamic-unpivot-stored-procedure
+
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

с одним столбцом во втором параметре работает

EXEC DynamicUnpivot
		N'SELECT * from  #TempT2'
		,@Column = N'col1'


Внимание вопрос:
как передать в третий параметр название еще нескольких столбцов?
	EXEC DynamicUnpivot
		N'SELECT * from  #TempT2'
		,@Column = N'col1'
		,@ColumnList = 'col2,col3'/*вот сюда как передать параметр?*/
10 дек 19, 15:09    [22036985]     Ответить | Цитировать Сообщить модератору
 Re: Динамический Unpivot с несколькими столбцами  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Yaroslav85, это табличная переменная:

    @ColumnList ColumnList READONLY,        -- List of column names. Combined with @Column to control which columns are unpivoted.


Объявляете табличную переменную, заполняете значениями, передаете в процедуру.
10 дек 19, 15:37    [22037015]     Ответить | Цитировать Сообщить модератору
 Re: Динамический Unpivot с несколькими столбцами  [new]
Yaroslav85
Member

Откуда:
Сообщений: 17
Minamoto

Объявляете табличную переменную, заполняете значениями, передаете в процедуру.

а можете пример кинуть?
я пробовал и так:

declare @T as table (name SYSNAME)
insert into @T select 'col2'
insert into @T select 'col3'
select * from @T
	EXEC DynamicUnpivot
		N'SELECT * from  #TempT2'
		,@Column = N'col1'
		,@ColumnList =  @T/*вот сюда как передать?*/


дает ошибку:
Msg 206, Level 16, State 2, Procedure DynamicUnpivot, Line 0 [Batch Start Line 21]
Operand type clash: table is incompatible with ColumnList
10 дек 19, 16:06    [22037058]     Ответить | Цитировать Сообщить модератору
 Re: Динамический Unpivot с несколькими столбцами  [new]
Yaroslav85
Member

Откуда:
Сообщений: 17
Minamoto,

спасибо . Погуглил ошибку - дошло :)

DECLARE @T AS ColumnList 
insert into @T select 'col2'
insert into @T select 'col3'
select * from @T

	EXEC DynamicUnpivot
		N'SELECT * from  #TempT2'
		,@Column = N'col1'
		,@ColumnList =  @T/*вот сюда как передать?*/

работает :)
10 дек 19, 16:11    [22037062]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить