如何对包含多个UNION的SQL查询执行Pivot行转列操作

你完全不需要重写原有包含12个UNION的查询逻辑，只需要将现有查询作为源数据集嵌套后执行行转列操作即可。

首先注意你提供的简化示例SQL存在语法问题：每个SELECT子句中'Complaints' as Label后缺少逗号，补全后再执行后续操作即可。

通用兼容写法（所有主流数据库均支持） #

不依赖数据库专属的PIVOT关键字，兼容性最强，直接用CASE WHEN聚合实现：

SELECT
  SUM(CASE WHEN Label = 'Complaints' THEN [count] ELSE 0 END) AS Complaints,
  SUM(CASE WHEN Label = 'Address' THEN [count] ELSE 0 END) AS Address,
  SUM(CASE WHEN Label = 'Inspections' THEN [count] ELSE 0 END) AS Inspections
  -- 剩余9个UNION对应的标签，按上面格式补全即可
FROM (
  -- 这里直接粘贴你原有补全语法后的完整UNION查询即可，不需要修改内部逻辑
  SELECT 1 as num, 'Complaints' as Label, SUM(1) as [count]
  FROM complaints
  UNION
  SELECT 2 as num, 'Address' as Label, SUM(1) as [count]
  FROM Address
  UNION 
  SELECT 3 as num, 'Inspections' as Label, SUM(1) as [count]
  FROM Inspections
  -- 剩下的9个UNION语句直接保留在这里
) AS source_data

执行后会直接输出一行，每个标签对应一列，就是你需要的横向排布效果。

SQL Server专属PIVOT语法实现 #

如果你使用的是SQL Server，可以用原生PIVOT关键字实现：

SELECT Complaints, Address, Inspections
-- 剩余9个标签按格式补全列名
FROM (
  SELECT Label, [count] 
  FROM (
    -- 这里直接粘贴你原有补全语法后的完整UNION查询即可
    SELECT 1 as num, 'Complaints' as Label, SUM(1) as [count]
    FROM complaints
    UNION
    SELECT 2 as num, 'Address' as Label, SUM(1) as [count]
    FROM Address
    UNION 
    SELECT 3 as num, 'Inspections' as Label, SUM(1) as [count]
    FROM Inspections
  ) AS union_data
) AS pivot_source
PIVOT (
  MAX([count]) -- 因为每个Label只有一个count值，用MAX/SUM都可以
  FOR Label IN (Complaints, Address, Inspections)
  -- 剩余9个标签补全在这里
) AS pivot_result

注意事项 #

• 原有UNION查询的内部JOIN、过滤逻辑完全不需要修改，直接整体嵌套即可
• 12个UNION的场景只需要对应补全12个CASE分支/12个PIVOT列项即可

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