How to Use Dynamic SQL PIVOT for Data Transformation with NULL or Empty String Handling?

Use the dynamic SQL Pivot for data conversion, process NULL or empty string

This article introduces how to convert the data in the table to the format of the category perspective, where the vacancy is represented as a NULL or empty string. This can be implemented by dynamic SQL Pivot query.

Use PIVOT to perform dynamic conversion

The following steps outline the process of creating a dynamic pivot query:

1. Create a temporary table: Create a temporary table to store the original data and define the necessary columns.
Generate dynamic list:
2. Different categories are used to query with sub -query and connect them into strings with commas separated. Build PIVOT query:
The generated lists are used as PIVOT parameters to build PIVOT query.
3. The polymerization function retrieves the maximum value of each category. Execute the query: MAX() Perform dynamic pivot queries to generate the result after the conversion.
4. Results
The generated table will include a column that represents the only category, and the date is used as a line. The empty value can be processed according to the required output through the NULL or empty string.

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 '


execute(@query)

Example results:

The above is the detailed content of How to Use Dynamic SQL PIVOT for Data Transformation with NULL or Empty String Handling?. For more information, please follow other related articles on the PHP Chinese website!