存在Totals列时如何使用PIVOT实现SQL查询结果转置？

报错原因 #

你收到的Invalid object name '#wellsPluggedTempTbl'报错是因为执行INSERT语句前没有提前创建对应的临时表，需要先定义表结构再写入数据。

完整实现代码 #

-- 先创建临时表
CREATE TABLE #wellsPluggedTempTbl(
    [Year] INT,
    [Oil Wells] INT,
    [Gas Wells] INT,
    [Service Wells] INT,
    [Dry Holes] INT,
    [Totals] INT
)

DECLARE @colsPivot AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

-- 插入按年份统计的油井数据
INSERT INTO #wellsPluggedTempTbl([Year], [Oil Wells], [Gas Wells], [Service Wells], [Dry Holes], Totals)
SELECT
    YEAR(wd.eventdate) AS [Year],
    ISNULL(COUNT(DISTINCT w_oil.WellID), 0) AS [Oil Wells],
    ISNULL(COUNT(DISTINCT w_gas.WellID), 0) AS [Gas Wells],
    ISNULL(COUNT(DISTINCT w_service.WellID), 0) AS [Service Wells],
    ISNULL(COUNT(DISTINCT w_dry.WellID), 0) AS [Dry Holes],
    ISNULL(COUNT(DISTINCT w_tot.WellID), 0) AS [Totals]
FROM HWellDate wd
    LEFT JOIN HWell w_oil ON (w_oil.PKey = wd.WellKey and w_oil.WellType = 'OW')
    LEFT JOIN HWell w_gas ON (w_gas.PKey = wd.WellKey and w_gas.WellType = 'GW')
    LEFT JOIN HWell w_service ON
    (
    w_service.PKey = wd.WellKey
    AND w_service.WellType IN ('WI','GI','GS','WD','WS','TW')
    )
    LEFT JOIN HWell w_dry ON (w_dry.PKey = wd.WellKey and w_dry.WellType = 'D')
    LEFT JOIN HWell w_tot ON w_tot.PKey = wd.WellKey
WHERE wd.comment = 'PA'
    AND wd.event = 'WELLSTATUS'
    AND (YEAR(wd.eventdate) >= (YEAR(GETDATE()) - 4) AND YEAR(wd.eventdate) <= YEAR(GETDATE()))
GROUP BY YEAR(wd.eventdate)

-- 动态生成倒序排列的年份列，适配近5年的查询规则，无需硬编码
SELECT @colsPivot = STUFF((
    SELECT DISTINCT ',' + QUOTENAME([Year]) 
    FROM #wellsPluggedTempTbl
    ORDER BY ',' + QUOTENAME([Year]) DESC
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 拼接UNPIVOT+PIVOT转置逻辑
SET @query = N'
SELECT WellType as [Well Types], ' + @colsPivot + N'
FROM 
(
    SELECT [Year], WellType, Cnt
    FROM #wellsPluggedTempTbl
    UNPIVOT
    (
        Cnt FOR WellType IN ([Oil Wells], [Gas Wells], [Service Wells], [Dry Holes], [Totals])
    ) AS unpvt
) AS src
PIVOT
(
    MAX(Cnt) FOR [Year] IN (' + @colsPivot + N')
) AS pvt
ORDER BY
    CASE WellType
        WHEN ''Totals'' THEN 5
        WHEN ''Dry Holes'' THEN 4
        WHEN ''Service Wells'' THEN 3
        WHEN ''Gas Wells'' THEN 2
        WHEN ''Oil Wells'' THEN 1
    END
'

-- 执行最终查询
EXEC sp_executesql @query

-- 清理临时表
DROP TABLE #wellsPluggedTempTbl

实现逻辑 #

1. 先创建临时表存储按年份统计的原始油井数据，解决插入报错问题
2. 动态生成需要转置的年份列，按倒序排列保证最新年份在最左侧，适配你取最近5年的查询逻辑，不需要每年修改硬编码
3. 先通过UNPIVOT将原表中不同井类型的列转为行数据，再通过PIVOT将年份转为表头列
4. 最后使用你提供的CASE排序规则，保证Totals行在结果最后一位。

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