无PIVOT函数时如何实现数据表透视转置？

原始数据表结构 #

目标数据表结构 #

无PIVOT函数的实现方案 #

因为数据库不支持PIVOT()函数，我们可以通过先逆透视转成窄表，再用条件聚合实现透视的方式完成转换，具体步骤如下：

步骤1：逆透视原始宽表 #

把宽表转换成YEAR_WEEK、CHANNEL、VALUE的三列窄表，用UNION ALL模拟逆透视逻辑：

SELECT YEAR_WEEK, 'CHANNEL_A' AS CHANNEL, CHANNEL_A AS VALUE FROM your_table
UNION ALL
SELECT YEAR_WEEK, 'CHANNEL_B' AS CHANNEL, CHANNEL_B AS VALUE FROM your_table
UNION ALL
SELECT YEAR_WEEK, 'CHANNEL_C' AS CHANNEL, CHANNEL_C AS VALUE FROM your_table
-- 如有更多渠道，继续添加对应UNION ALL语句

步骤2：条件聚合实现透视 #

基于上面的窄表，用CASE WHEN结合聚合函数（MAX或SUM均可，此处每个渠道每周仅一个值）生成目标结构：

SELECT
  CHANNEL,
  MAX(CASE WHEN YEAR_WEEK = '2022-11' THEN VALUE END) AS "2022-11",
  MAX(CASE WHEN YEAR_WEEK = '2022-12' THEN VALUE END) AS "2022-12",
  MAX(CASE WHEN YEAR_WEEK = '2022-13' THEN VALUE END) AS "2022-13"
  -- 如有更多周数，继续添加对应CASE WHEN语句
FROM (
  -- 嵌入步骤1的逆透视SQL
  SELECT YEAR_WEEK, 'CHANNEL_A' AS CHANNEL, CHANNEL_A AS VALUE FROM your_table
  UNION ALL
  SELECT YEAR_WEEK, 'CHANNEL_B' AS CHANNEL, CHANNEL_B AS VALUE FROM your_table
  UNION ALL
  SELECT YEAR_WEEK, 'CHANNEL_C' AS CHANNEL, CHANNEL_C AS VALUE FROM your_table
) AS unpivoted_data
GROUP BY CHANNEL;

补充说明 #

• 如果你的数据库支持UNPIVOT语法，可以直接用该语法简化逆透视步骤：

  SELECT YEAR_WEEK, CHANNEL, VALUE
  FROM your_table
  UNPIVOT (
    VALUE FOR CHANNEL IN (CHANNEL_A, CHANNEL_B, CHANNEL_C)
  ) AS unpivoted;
  

• 若周数或渠道数是动态的，需要结合数据库的动态SQL功能生成对应列或UNION分支，具体语法需根据数据库类型调整。

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