SQL Server Dynamic PIVOT Query

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 EXEC or sp_executesql command 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:

Year	Month	ProductA	ProductB	ProductC	ProductD	ProductE
2020	1	100.00	160.00	NULL	130.00	190.00
2021	1	NULL	110.00	170.00	NULL	140.00
2022	1	150.00	NULL	120.00	180.00	NULL

Result of the dynamic SQL Server Pivot Query

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

Advantages

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.

https://expert-only.com/en/t-sql/sql-server-pivot-query-example/