vali2009 vali2009 - 6 months ago 18
SQL Question

How to pivot rows into colums dynamically SQL Server

I have a request which returns something like this:

--------------------------
Tool | Week | Value
--------------------------
Test | 20 | 3
Sense | 20 | 2
Test | 19 | 2


And I want my input to look like this:

-------------------------
Tool | W20 | W19
-------------------------
Test | 3 | 2
Sense | 2 | null


Basically, for every week I need to have a new column. The number of week and of tools is dynamic.

I have tried many things but nothing worked. Anybody have a solution ?

Answer

Try this

CREATE table #tst (
Tool varchar(50), [Week] int, Value int
)

insert #tst 
values
('Test', 20, 3),
('Sense', 20,2),
('Test', 19, 2)

Here is the Dynamic Query:

DECLARE @col nvarchar(max), @query NVARCHAR(MAX)

SELECT @col = STUFF((SELECT DISTINCT ',' + QUOTENAME('W' + CAST([Week] as VARCHAR)) 
                from #tst
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @query = '
SELECT *
    FROM   (
        SELECT Tool,
               Value,
               ''W'' + CAST([Week] as VARCHAR) AS WeekNo
        FROM   #tst
    ) t
    PIVOT
    (
        MAX(t.Value)
        FOR WeekNo IN (' + @col + ')
    ) pv
 ORDER by Tool'

 EXEC (@query)

Result

Tool    W20  W19
=================
Sense   2    NULL
Test    3    2
Comments