{"id":29206,"date":"2023-11-13T06:22:00","date_gmt":"2023-11-13T05:22:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=29206"},"modified":"2023-11-15T16:03:08","modified_gmt":"2023-11-15T15:03:08","slug":"sql-server-dynamic-pivot-query","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-dynamic-pivot-query\/","title":{"rendered":"SQL Server Dynamic PIVOT Query"},"content":{"rendered":"\n

Script example to build a dynamic pivot query that adapts to the number of output columns.<\/em><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

Why use a dynamic PIVOT query in SQL ?<\/h3>\n\n\n\n

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.<\/p>\n\n\n\n

Main components of the dynamic PIVOT Query<\/h2>\n\n\n\n
    \n
  1. Static Columns<\/strong>: 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.<\/li>\n\n\n\n
  2. Pivot Columns<\/strong>: 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.<\/li>\n\n\n\n
  3. Dynamic SQL Execution<\/strong>: The final step is the execution of the dynamically constructed SQL query. This is typically done using the EXEC<\/code> or sp_executesql<\/code> command in SQL Server.<\/li>\n<\/ol>\n\n\n\n

    SQL Server Dynamic PIVOT Query Example<\/h2>\n\n\n\n

    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.<\/p>\n\n\n\n

    -- Step 1: declare the variables\nDECLARE\n   @StaticColumns     NVARCHAR(MAX),\n   @PivotColumns      NVARCHAR(MAX),\n   @DynamicPivotQuery NVARCHAR(MAX);\n\n-- Step 2: Identify the Static Columns\n-- Assuming Year and Month are our static columns, but you can modify this to add more\nSET @StaticColumns = N'Year, Month';\n\n-- Step 3: Identify the Dynamic Pivot Columns\nSELECT @PivotColumns = \n    STUFF((SELECT DISTINCT ',' + QUOTENAME(Product) \n           FROM SalesData \n           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');\n\n-- Step 4: Build the Dynamic Query\nSET @DynamicPivotQuery = \n  N'SELECT ' + @StaticColumns + ', ' + @PivotColumns + '\n    FROM (SELECT ' + @StaticColumns + ', Product, Amount FROM SalesData) AS SourceTable\n    PIVOT(SUM(Amount) FOR Product IN (' + @PivotColumns + ')) AS PivotTable';\n\n-- Step 5: Execute the Dynamic Query\nEXEC sp_executesql @DynamicPivotQuery;\n<\/pre>\n\n\n\n
    Result overview of the SQL Query<\/h3>\n\n\n\n
    Here’s the table represented in a simple, non-HTML format:<\/p>\n\n\n\n
    Year<\/th>Month<\/th>ProductA<\/th>ProductB<\/th>ProductC<\/th>ProductD<\/th>ProductE<\/th><\/tr><\/thead>
    2020<\/td>1<\/td>100.00<\/td>160.00<\/td>NULL<\/td>130.00<\/td>190.00<\/td><\/tr>
    2021<\/td>1<\/td>NULL<\/td>110.00<\/td>170.00<\/td>NULL<\/td>140.00<\/td><\/tr>
    2022<\/td>1<\/td>150.00<\/td>NULL<\/td>120.00<\/td>180.00<\/td>NULL<\/td><\/tr><\/tbody><\/table>
    Result of the dynamic SQL Server Pivot Query<\/em><\/figcaption><\/figure>\n\n\n\n
    SQL Ressources to use in the query example<\/h4>\n\n\n\n
    SQL code to create the sample table used in the code above:<\/p>\n\n\n\n
    CREATE TABLE SalesData (\n    [Year]  INT,\n\t[Month] INT,\n    Product NVARCHAR(50),\n    Amount DECIMAL(10, 2)\n);\n<\/pre>\n\n\n\n
    Insert statements to insert 20 rows in the table<\/p>\n\n\n\n
    INSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductA', 100.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductB', 110.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductC', 120.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductD', 130.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductE', 140.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductA', 150.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductB', 160.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2021, 1, 'ProductC', 170.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2022, 1, 'ProductD', 180.00);\nINSERT INTO SalesData ([Year], [Month], Product, Amount) VALUES (2020, 1, 'ProductE', 190.00);\n<\/pre>\n\n\n\n
    Advantages and Considerations<\/h2>\n\n\n\n
    Advantages<\/h3>\n\n\n\n