How do you perform a pivot operation using dynamic SQL?
FrankMl
Posted by FrankMl
Last Updated: June 29, 2024
SQL Pivot
Performing a pivot operation using dynamic SQL allows you to create a flexible SQL query that can adapt to different input data structures or requirements. Here’s how you can construct a dynamic SQL query to perform a pivot operation in SQL Server.
Step-by-step Guide to Create a Pivot with Dynamic SQL
1. Identify the Data: Determine the base table from which you want to pivot and identify the columns to be used as the row headers, column headers, and values. 2. Create the Dynamic SQL Statement: - Fetch the distinct values for the column that will become the new columns in your pivot table. - Construct the SQL query string dynamically using these values. 3. Execute the Dynamic SQL: Use EXEC or sp_executesql to execute the dynamic SQL query.
Example
Let’s say you have a Sales table structured like this: 
| Year | Product | Amount |
|------|---------|--------|
| 2020 | A       | 100    |
| 2020 | B       | 200    |
| 2021 | A       | 150    |
| 2021 | B       | 250    |
| 2022 | A       | 300    |
| 2022 | B       | 350    |
You want to pivot this data to show the amounts of each product by year.
Step 1: Fetch distinct products
DECLARE @cols AS NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Product), ', ')
FROM (SELECT DISTINCT Product FROM Sales) AS Products;
Step 2: Create the dynamic SQL string
Now, construct the SQL statement using the @cols variable. 
DECLARE @query AS NVARCHAR(MAX);
SET @query = N'SELECT Year, ' + @cols + '
FROM
(
    SELECT Year, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Product IN (' + @cols + ')
) AS PivotTable
ORDER BY Year;';
Step 3: Execute the SQL
Finally, run the SQL query using EXEC: 
EXEC sp_executesql @query;
Full Example
Here’s the complete code wrapped in a single script: 
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

-- Step 1: Get distinct product names
SELECT @cols = STRING_AGG(QUOTENAME(Product), ', ')
FROM (SELECT DISTINCT Product FROM Sales) AS Products;

-- Step 2: Create the dynamic SQL statement
SET @query = N'SELECT Year, ' + @cols + '
FROM
(
    SELECT Year, Product, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Product IN (' + @cols + ')
) AS PivotTable
ORDER BY Year;'; 

-- Step 3: Execute the dynamic SQL query
EXEC sp_executesql @query;
Notes
- STRING_AGG is available in SQL Server 2017 and later. If you're using an earlier version, you might need to use FOR XML PATH or a cursor to achieve similar results. - Always ensure that the data being pivoted does not lead to ambiguous column names. Using QUOTENAME helps to handle special characters in the column names. - Dynamic SQL can introduce SQL injection risks. Ensure that the values being passed are sanitized, especially if they come from user input.
Default Image