How to Dynamically Pivot Data in SQL Server Using a Dynamic Query?

Dynamic pivot data in SQL Server using dynamic queries

This article explains how to convert data from date, category, and amount columns into a more structured format that groups amounts by date and category.

For this purpose, dynamic pivot queries can be used. Pivot is a function of SQL Server that allows you to rotate a table's rows into columns. In this case, the pivot point is the date column and the resulting column will be the category.

<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);</code>

To build a dynamic pivot query, we first define two variables: @cols to hold the categories, and @query to store the final query.

<code class="language-sql">SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')</code>

Next, we build the @cols variables by concatenating the different categories with commas, making sure they are enclosed in double quotes.

<code class="language-sql">set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '</code>

Finally, we assign the query string to @query. The query starts by selecting the date column and all categories as columns. It then uses PIVOT to rotate the data, with the date column as the row header and the category as the column header.

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

Executing @query will return the desired results, pivoting the data as needed.

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

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