Absolutely! You don’t have to lock yourself into hardcoding values in the IN clause of a Pivot operation inside a stored procedure. Dynamic SQL is the perfect solution here—it lets you automatically pull the distinct values you need for your pivot columns on the fly. Let me break this down step by step with a practical example.

First, the Static Pivot Problem #

Let’s say you have a SalesData table with salesperson names, products, and amounts. A typical static pivot might look like this:

SELECT *
FROM (
    SELECT SalesPerson, Product, Amount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Product IN ([Laptop], [Phone], [Tablet]) -- Hardcoded values = inflexible
) AS PivotTable;

The issue here is obvious: if you add a new product (like [Headphones]), this query won’t include it until you manually update the IN clause. Dynamic pivot fixes that.

Step 1: Automatically Get Pivot Column Values #

First, we need to fetch all distinct product names from the table and format them as quoted column names (to handle spaces or special characters).

For SQL Server 2017+ (using STRING_AGG): #

DECLARE @PivotColumns NVARCHAR(MAX);

SELECT @PivotColumns = STRING_AGG(QUOTENAME(Product), ', ')
FROM (SELECT DISTINCT Product FROM SalesData) AS UniqueProducts;

For older SQL Server versions (using STUFF + FOR XML): #

If you’re on a version before 2017, use this method instead:

DECLARE @PivotColumns NVARCHAR(MAX);

SELECT @PivotColumns = STUFF((
    SELECT ', ' + QUOTENAME(Product)
    FROM (SELECT DISTINCT Product FROM SalesData) AS UniqueProducts
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

Step 2: Build the Dynamic Pivot Query #

Next, we construct the full pivot query as a string, inserting our dynamically generated columns into the IN clause:

DECLARE @DynamicPivotSQL NVARCHAR(MAX);

SET @DynamicPivotSQL = N'
SELECT *
FROM (
    SELECT SalesPerson, Product, Amount
    FROM SalesData
) AS SourceTable
PIVOT (
    SUM(Amount)
    FOR Product IN (' + @PivotColumns + ')
) AS PivotTable;';

Step 3: Execute the Dynamic SQL #

Finally, run the dynamic query using sp_executesql (this is safer than EXEC because it handles parameterization if you need it later):

EXEC sp_executesql @DynamicPivotSQL;

Wrap It All in a Stored Procedure #

Here’s how to package this into a reusable stored procedure, with error handling for cases where there are no products to pivot:

CREATE PROCEDURE dbo.GetDynamicSalesPivot
AS
BEGIN
    SET NOCOUNT ON;

    -- Fetch distinct pivot columns
    DECLARE @PivotColumns NVARCHAR(MAX);

    SELECT @PivotColumns = STRING_AGG(QUOTENAME(Product), ', ')
    FROM (SELECT DISTINCT Product FROM SalesData) AS UniqueProducts;

    -- Handle empty case to avoid runtime errors
    IF @PivotColumns IS NULL
    BEGIN
        PRINT 'No products found to pivot. Check your SalesData table.';
        RETURN;
    END

    -- Build dynamic query
    DECLARE @DynamicPivotSQL NVARCHAR(MAX);

    SET @DynamicPivotSQL = N'
    SELECT *
    FROM (
        SELECT SalesPerson, Product, Amount
        FROM SalesData
    ) AS SourceTable
    PIVOT (
        SUM(Amount)
        FOR Product IN (' + @PivotColumns + ')
    ) AS PivotTable;';

    -- Execute the query
    EXEC sp_executesql @DynamicPivotSQL;
END;

Key Notes #

• SQL Injection Safety: Using QUOTENAME() ensures that any special characters or malicious input in product names are properly escaped, so you don’t have to worry about injection risks here.
• Database Compatibility: If you’re using MySQL, the approach is similar but uses GROUP_CONCAT() instead of STRING_AGG, and executes with PREPARE/EXECUTE. For PostgreSQL, you’d use string_agg() and EXECUTE.
• Add Parameters: You can easily extend this stored procedure to accept parameters (e.g., a date range filter) by adding them to the dynamic query and passing them via sp_executesql.

内容的提问来源于stack exchange，提问作者AlanPear