How to Convert Rows to Columns in SQL Server Using PIVOT?

Using SQL Server's PIVOT Function to Restructure Data

SQL Server's powerful PIVOT function offers a streamlined way to transform data from a row-based structure to a column-based one. This is particularly useful for creating more easily readable and analyzed tabular reports.

Imagine a table with store numbers, week numbers, and a value (let's call it xCount). The goal is to reorganize this data so store numbers are listed vertically (rows) and week numbers horizontally (columns).

Static PIVOT (Known Week Numbers):

If you already know the specific week numbers you need, a straightforward PIVOT query can be used:

SELECT *
FROM (
  SELECT store, week, xCount
  FROM yt
) src
PIVOT (SUM(xcount) FOR week IN ([1], [2], [3])) piv;

This query sums the xCount values for each store and week. The IN clause specifies the weeks ([1], [2], [3] in this example).

Dynamic PIVOT (Unknown Week Numbers):

When the week numbers are dynamic (not known beforehand), a more flexible approach is needed:

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);

This dynamic query first builds a comma-separated list of unique week numbers from the yt table. This list is then incorporated into a larger query that uses the PIVOT function to create the desired column-based output. The result is a pivot table showing stores as rows and weeks as columns, with corresponding xCount values. This method adapts to any number of weeks present in the data.

The above is the detailed content of How to Convert Rows to Columns in SQL Server Using PIVOT?. For more information, please follow other related articles on the PHP Chinese website!