How Can I Use SQL Server's PIVOT Function to Transform Rows into Columns?

Mastering SQL Server's PIVOT Function for Data Transformation

SQL Server's PIVOT function offers a powerful way to restructure data, transforming rows into columns for improved analysis and reporting. This is especially helpful when you need to present data in a more user-friendly, tabular format.

Imagine a temporary table holding sales figures, with columns for store number, week number, and sales amount. To display this data with stores listed vertically and weeks horizontally, follow these steps:

1. Dynamically Identify Week Numbers

Because the week numbers are variable, we first need to determine the unique week numbers present in the dataset. This can be achieved using:

<code class="language-sql">SELECT DISTINCT Week FROM yt;</code>

2. Dynamic SQL Query Construction

Next, we construct the dynamic SQL query. This involves creating a string containing the column names for the PIVOT operation. The following code generates this string:

<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,'');</code>

3. Building the PIVOT Query

Now, we build the PIVOT query itself, incorporating the dynamically generated column string:

<code class="language-sql">SET @query = 'SELECT store,' + @cols + ' FROM 
             (
                SELECT store, week, xCount
                FROM yt
            ) x
            PIVOT 
            (
                SUM(xCount)
                FOR week IN (' + @cols + ')
            ) p;';</code>

4. Executing the PIVOT Query

Finally, execute the dynamic PIVOT query using:

<code class="language-sql">EXECUTE(@query);</code>

Results:

The output will be a table showing store numbers in the first column and week numbers as column headers. The corresponding sales amounts will populate the table cells, summarizing the original data effectively.

By utilizing SQL Server's PIVOT function, you can efficiently convert row-oriented data into a more easily interpreted and analyzed columnar format, simplifying data reporting and analysis tasks.

The above is the detailed content of How Can I Use SQL Server's PIVOT Function to Transform Rows into Columns?. For more information, please follow other related articles on the PHP Chinese website!