Thursday, August 1, 2024

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:

YearQuarterSalesAmount
2022Q1100
2022Q2150
2022Q3200
2022Q4250
2023Q1110
2023Q2160
2023Q3210
2023Q4260

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.

No comments:

Post a Comment