Dynamic Pivoting in t-sql

 Dynamic pivoting in T-SQL allows you to create pivot tables where the columns are not known beforehand and need to be generated dynamically based on the data. This is useful when the set of values to be pivoted changes frequently or is not known at compile time.

Here's a step-by-step example to demonstrate dynamic pivoting in T-SQL:

Scenario

Let's say we have a Sales table with the following data:

Year	Quarter	SalesAmount
2022	Q1	100
2022	Q2	150
2022	Q3	200
2022	Q4	250
2023	Q1	110
2023	Q2	160
2023	Q3	210
2023	Q4	260

We want to dynamically pivot this data so that each quarter becomes a column.

Steps for Dynamic Pivoting

1. Retrieve the unique values for the columns to pivot.
2. Construct the dynamic SQL query.
3. Execute the dynamic SQL query.

Step 1: Retrieve Unique Column Values

First, we need to get the unique values from the Quarter column:

DECLARE @Columns NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(Quarter), ',') 
FROM (SELECT DISTINCT Quarter FROM Sales) AS Quarters;

Step 2: Construct the Dynamic SQL Query

Next, we construct the dynamic SQL query using the retrieved column values:

SET @SQL = '
SELECT Year, ' + @Columns + '
FROM
(
    SELECT Year, Quarter, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter IN (' + @Columns + ')
) AS PivotTable
ORDER BY Year;
';

Step 3: Execute the Dynamic SQL Query

Finally, execute the dynamic SQL query:

EXEC sp_executesql @SQL;

Complete Example

Putting it all together, the complete T-SQL script looks like this:

-- Step 1: Retrieve unique column values
DECLARE @Columns NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(Quarter), ',') 
FROM (SELECT DISTINCT Quarter FROM Sales) AS Quarters;

-- Step 2: Construct the dynamic SQL query
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '
SELECT Year, ' + @Columns + '
FROM
(
    SELECT Year, Quarter, SalesAmount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Quarter IN (' + @Columns + ')
) AS PivotTable
ORDER BY Year;
';

-- Step 3: Execute the dynamic SQL query
EXEC sp_executesql @SQL;

Explanation

1. Retrieve Unique Column Values: The STRING_AGG function is used to concatenate the distinct quarter values, separated by commas. The QUOTENAME function ensures that the column names are properly escaped.

2. Construct the Dynamic SQL Query: The @Columns variable is used to dynamically insert the column names into the PIVOT clause of the SQL query.

3. Execute the Dynamic SQL Query: The sp_executesql stored procedure is used to execute the dynamically constructed SQL query.

This approach allows you to pivot data dynamically based on the actual values present in the dataset, making it flexible and adaptable to changing data.