S
S
Sergey2019-06-28 20:29:42
Transact SQL
Sergey, 2019-06-28 20:29:42

How to pass a variable to PIVOT (T-SQL) instead of listing columns?

I'm trying to make a dynamic PIVOT. But it is not possible to pass the name of the columns through a variable. There is a table like:
Article | Name | Quantity | Month
1001 Item 1 234 Jan
1001 Item 1 435 Feb
1001 Item 1 213 Jan
1002 Item 2 123 Jan
1002 Item 2 32 Feb
Query like below works fine:

SELECT [Артикул], [Январь], [Февраль] FROM [TP] 
    PIVOT (sum([Количество]) FOR [Месяц] 
    IN ([Январь], [Февраль])) as pvt

But how to make a similar request work but with a variable
DECLARE @column_month NVARCHAR(MAX);
    SET @column_month = '[Январь], [Февраль]'

    SELECT [Артикул], @column_month FROM [TP] 
    PIVOT (sum([Количество]) FOR [Месяц] 
    IN (@column_month)) as pvt

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
Konstantin Tsvetkov, 2019-06-28
@tsklab

SQL Server dynamic PIVOT query .

S
Sergey, 2019-06-28
@googlgan

Understood. You need to wrap the EXECUTE request and write the variables according to the example:

DECLARE @column_month NVARCHAR(MAX);
    SET @column_month = '[Январь], [Февраль]'

    EXECUTE('
    SELECT [Артикул],  ' + @column_month  + ' FROM [TP] 
    PIVOT (sum([Количество]) FOR [Месяц] 
    IN (' + @column_month  + ')) as pvt
    ')

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question