Solution
Popot function for known column values:For the predefined column values (the number of weeks in this example), you can directly use the PIVOT function:
Dynamically generated Perspective columns:
select * from ( select store, week, xCount from yt ) src pivot ( sum(xcount) for week in ([1], [2], [3]) ) piv;
<:> Result:
Both methods produce the same result:
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);
| Shop | 1 | 2 | 3 | | 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 || 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |The above is the detailed content of How to Convert Rows to Columns in SQL Server Using the PIVOT Function?. For more information, please follow other related articles on the PHP Chinese website!