Home >Database >Mysql Tutorial >How Can I Pivot Rows into Columns Using SQL Server's PIVOT Function, Handling Both Static and Dynamic Week Numbers?
SQL Server's PIVOT function: Convert the line to column to handle static and dynamic weekly number
Background:
You have a temporary table containing multiple columns, including the store number, weekly number and corresponding total. The number of weeks is dynamic, which makes it challenging to see the data into the format.Solution:
To use the
function to convert the line into a list in SQL Server, please follow the steps below:
PIVOT
<.> 1. Static week number:
If the number of weeks is known, please use the following hard coding query:
<.> 2. Dynamic week number:
<code class="language-sql">SELECT * FROM ( SELECT store, week, xCount FROM yt ) src PIVOT ( SUM(xcount) FOR week IN ([1], [2], [3]) ) piv;</code>
If the week is dynamic, please use the dynamic SQL:
Result:
<code class="language-sql">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);</code>Both query returns a perspective table, the store number is on the left, and the weekly number is on the top. Each cell contains the sum of the corresponding store and the "XCount" of the weekly number.
The above is the detailed content of How Can I Pivot Rows into Columns Using SQL Server's PIVOT Function, Handling Both Static and Dynamic Week Numbers?. For more information, please follow other related articles on the PHP Chinese website!