Script example to build a dynamic pivot query that adapts to the number of output columns.
Writing a dynamic PIVOT query in SQL Server is a powerful way to transform and transpose data from rows into columns. Above all when the number of elements is not known beforehand or it changes regularly. Indeed, it is useful in scenarios where the data schema is dynamic or when generating reports and summaries from a variable dataset.
Why use a dynamic PIVOT query in SQL ?
A dynamic PIVOT query involves creating a SQL query string programmatically, which can pivot rows into an unknown number of columns. This is achieved by dynamically determining the unique values that will serve as the column headers in the pivoted table.
Main components of the dynamic PIVOT Query
- Static Columns: These are the columns in the data that will remain unchanged in the pivot transformation. The query dynamically accommodates these columns, allowing flexibility in selecting which columns to include.
- Pivot Columns: The essence of the dynamic pivot lies in determining which columns are to be transformed. This part of the query dynamically identifies unique values in a specified column, which then become the headers of the new columns in the pivoted table.
- Dynamic SQL Execution: The final step is the execution of the dynamically constructed SQL query. This is typically done using the
sp_executesqlcommand in SQL Server.
SQL Server Dynamic PIVOT Query Example
Here is the SQL query to dynamically take the values from the column and build the PIVOT query statement. To go even further, it is also possible to add variables to change the aggregation operator, the source table name and the source column.
-- Step 1: declare the variables DECLARE @StaticColumns NVARCHAR(MAX), @PivotColumns NVARCHAR(MAX), @DynamicPivotQuery NVARCHAR(MAX); -- Step 2: Identify the Static Columns -- Assuming Year and Month are our static columns, but you can modify this to add more SET @StaticColumns = N'Year, Month'; -- Step 3: Identify the Dynamic Pivot Columns SELECT @PivotColumns = STUFF((SELECT DISTINCT ',' + QUOTENAME(Product) FROM SalesData FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- Step 4: Build the Dynamic Query SET @DynamicPivotQuery = N'SELECT ' + @StaticColumns + ', ' + @PivotColumns + ' FROM (SELECT ' + @StaticColumns + ', Product, Amount FROM SalesData) AS SourceTable PIVOT(SUM(Amount) FOR Product IN (' + @PivotColumns + ')) AS PivotTable'; -- Step 5: Execute the Dynamic Query EXEC sp_executesql @DynamicPivotQuery;
Result overview of the SQL Query
Here’s the table represented in a simple, non-HTML format:
SQL Ressources to use in the query example
SQL code to create the sample table used in the code above:
CREATE TABLE SalesData ( [Year] INT, [Month] INT, Product NVARCHAR(50), Amount DECIMAL(10, 2) );
Insert statements to insert 20 rows in the table
INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductA', 100.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductB', 110.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductC', 120.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductD', 130.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductE', 140.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductA', 150.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductB', 160.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductC', 170.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductD', 180.00); INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductE', 190.00);
Advantages and Considerations
- Flexibility: It offers the ability to handle varying numbers of columns without the need to modify the query for each specific case.
- Scalability: Particularly useful in dealing with large and complex datasets where the schema may vary or evolve over time.
Considerations and risks
- SQL Injection Risk: As with any dynamic SQL, there’s an inherent risk of SQL injection. Proper validation and sanitization of inputs are crucial.
- Performance: Dynamic queries can be more complex and potentially slower than static queries. Performance considerations should be taken into account, especially with large datasets.
A good way to manage changing data structures
Using a dynamic PIVOT query in SQL Server is an really useful tool, especially for data transformation and reporting. It provides a high degree of flexibility and adaptability for data analysis, making them a go-to technique for SQL Server developers and database administrators.
We often face dynamic code in large ETL frameworks, where the integration T-SQL code is designed and reused in many components of an automated project. Of course before using dynamic code it is highly recommended to use static code and test it before making it scalable. For instance, here the static version of the SQL Server PIVOT query.