SQL Server 2005动态透视查询中如何修改指定动态列名

Got it, let's work through this problem step by step. Since you're on SQL Server 2005 SP3, we can't use newer features like STRING_AGG, but we can rely on tried-and-true tricks for dynamic SQL and string concatenation to get what your sales team needs.

Step 1: Build Two Key Column Lists #

First, we need to generate two critical pieces of information from your temporary table #CombinedSales:

1. A list of raw column names (for the PIVOT clause's IN statement)
2. A list of those same columns with the date_ prefix stripped (for the final SELECT output as user-friendly aliases)

Since temporary tables are stored in tempdb, we'll query tempdb.sys.columns to fetch the relevant column names:

DECLARE @RawPivotColumns NVARCHAR(MAX), @AliasedPivotColumns NVARCHAR(MAX)

-- 1. Raw columns (used in the PIVOT IN clause)
SELECT @RawPivotColumns = COALESCE(@RawPivotColumns + ', ', '') 
    + QUOTENAME(name)
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#CombinedSales')
AND name LIKE '%date_WeeklySales2017%'

-- 2. Aliased columns (used in SELECT to remove 'date_' prefix)
SELECT @AliasedPivotColumns = COALESCE(@AliasedPivotColumns + ', ', '') 
    + QUOTENAME(name) + ' AS ' + QUOTENAME(REPLACE(name, 'date_', ''))
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..#CombinedSales')
AND name LIKE '%date_WeeklySales2017%'

Step 2: Construct and Run the Dynamic Pivot Query #

Next, we'll plug these two lists into a dynamic pivot query. You'll need to adjust the aggregation logic (like SUM(WeeklySales)) and the pivot column ([COL_A]) to match your actual data and requirements:

DECLARE @DynamicPivotSQL NVARCHAR(MAX)

SET @DynamicPivotSQL = N'
SELECT 
    WeekNumber,
    WeekStart,
    StoreNumber,
    Address,
    City,
    Province,
    ' + @AliasedPivotColumns + '
FROM #CombinedSales
PIVOT (
    -- Replace SUM(WeeklySales) with your actual aggregation (AVG, COUNT, etc.)
    SUM(WeeklySales)
    -- Replace [COL_A] with the column you''re pivoting on
    FOR [COL_A] IN (' + @RawPivotColumns + ')
) AS PivotResult'

-- Execute the dynamic SQL
EXEC sp_executesql @DynamicPivotSQL

Important Notes for SQL Server 2005: #

• QUOTENAME is non-negotiable: It wraps column names in square brackets to avoid syntax errors if your column names have special characters or match SQL reserved words.
• COALESCE for concatenation: Since SQL Server 2005 doesn't support STRING_AGG, we use COALESCE to build our comma-separated lists without NULL values.
• Double single quotes: Notice the '' in your''re—this is how you escape a single quote inside a dynamic SQL string in SQL Server.

Example Outcome #

If your temporary table has columns like date_WeeklySales2017_Q1 and date_WeeklySales2017_Q2, the final query will output columns named WeeklySales2017_Q1 and WeeklySales2017_Q2—exactly what your sales team asked for.

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