SQL如何实现Measure列名转行值、Location行值转列名的行列转换
行转列SQL实现方案
核心逻辑
该需求为典型的两步行列转换操作:先将Measure1/Measure2/Measure3三个度量列逆透视转换为行值,再将Location字段的A/B/C取值透视转换为列名。
第一步:逆透视(宽表转长表)
将原表的宽表结构拆解为长表,新增Measure(存储度量名称)、MeasureValue(存储度量对应值)两个字段,保留Year/Account/Location原有字段。
通用兼容写法(支持所有SQL引擎)
用UNION ALL逐行拼接每个度量的转换结果:
SELECT Year, Account, Location, 'Measure1' AS Measure, Measure1 AS MeasureValue FROM 业务表 UNION ALL SELECT Year, Account, Location, 'Measure2' AS Measure, Measure2 AS MeasureValue FROM 业务表 UNION ALL SELECT Year, Account, Location, 'Measure3' AS Measure, Measure3 AS MeasureValue FROM 业务表
专用语法写法(更简洁)
支持UNPIVOT语法的引擎(SQL Server、Oracle、BigQuery等)可直接使用:
SELECT Year, Account, Location, Measure, MeasureValue FROM 业务表 UNPIVOT ( MeasureValue FOR Measure IN (Measure1, Measure2, Measure3) ) AS unpvt
第二步:透视(长表转目标宽表)
基于逆透视后的长表,通过条件聚合将Location的A/B/C取值转为对应列,非匹配行自动置空。
通用兼容写法
用CASE WHEN做条件聚合实现:
SELECT Year, Account, Measure, MAX(CASE WHEN Location = 'A' THEN MeasureValue END) AS A, MAX(CASE WHEN Location = 'B' THEN MeasureValue END) AS B, MAX(CASE WHEN Location = 'C' THEN MeasureValue END) AS C FROM ( -- 嵌套第一步的逆透视逻辑 SELECT Year, Account, Location, 'Measure1' AS Measure, Measure1 AS MeasureValue FROM 业务表 UNION ALL SELECT Year, Account, Location, 'Measure2' AS Measure, Measure2 AS MeasureValue FROM 业务表 UNION ALL SELECT Year, Account, Location, 'Measure3' AS Measure, Measure3 AS MeasureValue FROM 业务表 ) t -- 若需要匹配示例中的特定行,可在此处增加过滤条件 -- WHERE (Account = '123a' AND Measure = 'Measure1') -- OR (Account = '234b' AND Measure = 'Measure2') -- OR (Account = '122c' AND Measure = 'Measure3') GROUP BY Year, Account, Measure -- 可选:过滤掉全空的无效行 HAVING MAX(CASE WHEN Location = 'A' THEN MeasureValue END) IS NOT NULL OR MAX(CASE WHEN Location = 'B' THEN MeasureValue END) IS NOT NULL OR MAX(CASE WHEN Location = 'C' THEN MeasureValue END) IS NOT NULL
专用语法写法
支持PIVOT语法的引擎可直接使用:
SELECT Year, Account, Measure, A, B, C FROM ( -- 嵌套第一步的逆透视结果 SELECT Year, Account, Location, Measure, MeasureValue FROM 业务表 UNPIVOT (MeasureValue FOR Measure IN (Measure1, Measure2, Measure3)) AS unpvt ) t -- 若需要匹配示例中的特定行,可在此处增加过滤条件 -- WHERE (Account = '123a' AND Measure = 'Measure1') -- OR (Account = '234b' AND Measure = 'Measure2') -- OR (Account = '122c' AND Measure = 'Measure3') PIVOT ( MAX(MeasureValue) FOR Location IN ('A' AS A, 'B' AS B, 'C' AS C) ) AS pvt
示例空行补充
你给出的示例结果中存在仅Year有值的空行,若为业务要求的展示格式,可在最终结果中通过UNION ALL拼接空行逻辑,再用ORDER BY调整行顺序:
-- 前面的透视结果SQL占位 SELECT Year, Account, Measure, A, B, C FROM 透视结果表 UNION ALL -- 拼接空行 SELECT DISTINCT Year, NULL AS Account, NULL AS Measure, NULL AS A, NULL AS B, NULL AS C FROM 业务表 ORDER BY Year, Account NULLS FIRST, Measure
内容的提问来源于stack exchange,提问作者user15058570
相关产品推荐
相关产品推荐

