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
- Retrieve the unique values for the columns to pivot.
- Construct the dynamic SQL query.
- 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
Retrieve Unique Column Values: The
STRING_AGG
function is used to concatenate the distinct quarter values, separated by commas. TheQUOTENAME
function ensures that the column names are properly escaped.Construct the Dynamic SQL Query: The
@Columns
variable is used to dynamically insert the column names into thePIVOT
clause of the SQL query.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