How to Dynamically Pivot Data in SQL Server?

SQL Server Dynamic Pivot Query

This article describes how to convert a dataset from vertical format to horizontal format, with categories as columns and dates as rows. This is usually achieved using the PIVOT operator.

The following SQL code initializes the dataset in a temporary table named temp. Dynamic SQL is used to build PIVOT queries. @colsVariable concatenates different categories into a comma separated string. @queryVariables build the actual PIVOT query, grouping by date and pivoting by category.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

Executing this query will produce the desired results:

<code>日期                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL</code>

This dynamic approach allows categories to be adjusted as needed without changing the query structure. Remember to delete temporary tables after use to avoid performance issues.

The above is the detailed content of How to Dynamically Pivot Data in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!