Home > Database > Mysql Tutorial > How to Use Dynamic SQL PIVOT for Data Transformation with NULL or Empty String Handling?

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

Barbara Streisand
Release: 2025-01-25 18:42:11
Original
929 people have browsed it

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.
  2. Generate dynamic list:
  3. Different categories are used to query with sub -query and connect them into strings with commas separated. Build PIVOT query:
  4. The generated lists are used as PIVOT parameters to build PIVOT query.
  5. 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.
  6. Results
  7. 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.
<code class="language-sql">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)</code>
Copy after login
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!

source:php.cn
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