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!