Home > Database > Mysql Tutorial > How Can I Use SQL Server's PIVOT Function to Transform Rows into Columns?

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

Patricia Arquette
Release: 2025-01-25 12:17:14
Original
819 people have browsed it

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:

SELECT DISTINCT Week FROM yt;
Copy after login

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:

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,'');
Copy after login

3. Building the PIVOT Query

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

SET @query = 'SELECT store,' + @cols + ' FROM 
             (
                SELECT store, week, xCount
                FROM yt
            ) x
            PIVOT 
            (
                SUM(xCount)
                FOR week IN (' + @cols + ')
            ) p;';
Copy after login

4. Executing the PIVOT Query

Finally, execute the dynamic PIVOT query using:

EXECUTE(@query);
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template