How Can I Dynamically Pivot Multiple Columns in SQL Server?

Dynamic Pivot with Multiple Columns in SQL Server

In SQL Server, pivoting data allows you to transpose rows into columns, enabling more intuitive data analysis. When dealing with multiple columns, employing a dynamic approach can cater to changing column names or values.

Unpivoting Data

To dynamically pivot data with multiple columns, begin by unpivoting the relevant columns using either the UNPIVOT function or CROSS APPLY. This process transforms multiple columns into rows, making them easier to manipulate.

Dynamic PIVOT Function

Once unpivoted, you can employ the PIVOT function to create the pivoted table. However, the column names in the PIVOT function must align with the unpivoted data. To achieve this dynamically, concatenate the columns' values with their respective metadata using the following steps:

1. Select the columns to be pivoted and specify their concatenated names in the @cols variable.
2. Construct the dynamic query in the @query variable by incorporating the concatenated column names.
3. Use sp_executesql to execute the dynamic SQL query and obtain the pivoted result.

Example

Consider the following sample table with Year, Type, Total, and Volume columns:

ID	YEAR	TYPE	TOTAL	VOLUME
DD1	2008	A	1000	10
DD1	2008	B	2000	20
DD1	2008	C	3000	30

Using the dynamic pivot approach, you can create a pivoted table with the following structure:

ID	2008_A_Total	2008_A_Volume	2008_B_Total	2008_B_Volume	2008_C_Total	2008_C_Volume
DD1	1000	10	2000	20	3000	30

By combining unpivoting and dynamic pivot techniques, you can effectively transform and analyze data with varying columns in SQL Server.

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