How to Perform Dynamic Pivoting with Multiple Columns in SQL Server 2008?

Dynamic PIVOTING with Multiple Columns in SQL Server

Overview

SQL Server provides limited options for pivoting data across multiple columns. This article explores how to achieve dynamic pivoting in SQL Server 2008 with more than one column using a combination of unpivoting and dynamic SQL.

Understanding the Problem

The goal is to transform a table with multiple columns into a more user-friendly format where the values of the columns become the column headers. In this case, the table contains the following data:

ID	YEAR	TYPE	TOTAL	VOLUME
DD1	2008	A	1000	10
DD1	2008	B	2000	20
DD1	2008	C	3000	30
DD1	2009	A	4000	40
DD1	2009	B	5000	50
DD1	2009	C	6000	60
DD2	2008	A	7000	70
DD2	2008	B	8000	80
DD2	2008	C	9000	90
DD2	2009	A	10000	100
DD2	2009	B	11000	110
DD2	2009	C	1200	120

The desired output is:

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

Solution

Unpivoting the Data

To pivot multiple columns, it's first necessary to unpivot the data. This means transforming the columns into rows, with a col column identifying the original column name and a value column containing the value.

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

Dynamic PIVOT Function

The next step is to use the PIVOT function to transform the unpivoted data into the desired format. However, SQL Server has limitations when pivoting multiple columns. To overcome this, dynamic SQL is used to construct the PIVOT query at runtime.

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

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+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, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+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 '

execute sp_executesql @query;

Conclusion

By combining unpivoting with dynamic SQL, it's possible to perform dynamic pivoting across multiple columns in SQL Server 2008. This provides a flexible solution for transforming data into a more user-friendly format.

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