How to Dynamically Pivot Multiple Columns in SQL Server?

Dynamic Pivot Transformation on Multiple Columns in SQL Server

Overview

This article addresses the challenge of performing a dynamic pivot operation on multiple columns in Microsoft SQL Server. The goal is to transform data into a pivot table format, with rows representing distinct values in a specified column and columns representing values from other columns.

Unpivoting the Data

Before performing the pivot operation, the data must be unpivoted. This process reshapes the data by converting multiple columns into rows. In this case, the Total and Volume columns are unpivoted.

SELECT ID, 
    CONCAT(T_YEAR, '_', T_TYPE, '_', COL) AS COL, 
    VALUE
FROM ATM_TRANSACTIONS t
CROSS APPLY
(
    SELECT 'total', TOTAL UNION ALL
    SELECT 'volume', VOLUME
) c (COL, VALUE)

Generating the Dynamic Pivot Query

Once the data is unpivoted, the dynamic pivot query can be constructed. This query builds the necessary columns for the pivot table dynamically based on the unpivoted data.

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

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONCAT(T_YEAR, '_', T_TYPE, '_', COL)) 
                    FROM ATM_TRANSACTIONS t
                    CROSS APPLY
                    (
                        SELECT 'total', 1 UNION ALL
                        SELECT 'volume', 2
                    ) c (COL, SO)
                    GROUP BY COL, SO, T_TYPE, T_YEAR
                    ORDER BY T_YEAR, T_TYPE, SO
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = 'SELECT ID,' + @cols + ' 
            FROM 
            (
                SELECT ID, 
                    CONCAT(T_YEAR, ''_''+T_TYPE+''_''+COL) AS COL, 
                    VALUE
                FROM ATM_TRANSACTIONS t
                CROSS APPLY
                (
                    SELECT ''total'', TOTAL UNION ALL
                    SELECT ''volume'', VOLUME
                ) c (COL, VALUE)
            ) x
            PIVOT 
            (
                MAX(VALUE)
                FOR COL IN (' + @cols + ')
            ) p ';

Executing the Dynamic Query

The dynamic query is executed using the sp_executesql stored procedure. This procedure takes the query generated by the dynamic SQL code and executes it.

EXECUTE SP_EXECUTESQL @QUERY;

Result

The output of the dynamic pivot operation is a pivot table with distinct ID values as rows and columns representing the unpivoted Total and Volume values for each combination of T_YEAR and T_TYPE. The result will be as follows:

ID	2008_A_TOTAL	2008_A_VOLUME	2008_B_TOTAL	2008_B_VOLUME	2008_C_TOTAL	2008_C_VOLUME	2009_A_TOTAL	2009_A_VOLUME	2009_B_TOTAL	2009_B_VOLUME	2009_C_TOTAL	2009_C_VOLUME
DD1	1000	10	2000	20	3000	30	4000	40	5000	50	6000	60
DD2	7000	70	8000	80	9000	90	10000	100	11000	110	12000	120

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