You need to enable JavaScript to run this app. SQL如何实现Measure列名转行值、Location行值转列名的行列转换
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 09:15:08