Convert rows to columns using the PIVOT function in SQL Server

question

Pivot tables in SQL Server provide a powerful way to transpose data from rows to columns. However, users may encounter challenges when building the correct query.

Solution

Use the PIVOT function for known column values:

For predefined column values ​​(in this case the number of weeks), you can use the PIVOT function directly:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Dynamically generate perspective column values:

To handle unknown column values ​​(such as dynamic weeks), a combination of dynamic SQL and window functions can be used:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ','   QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,'   @cols   ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in ('   @cols   ')
            ) p '

execute(@query);

result:

Both methods produce the same result:

| Shop | 1 | 2 | 3 |

| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |