如何将SQL查询结果行列转换：将周1-7设为列、统计字段设为行

实现思路 #

要得到你想要的输出格式，需要完成两次数据结构转换：

1. 逆透视（Unpivot）：将原表中avg_pts、avg_fgm等统计类列转为行数据，得到Stats（统计项名称）、Value（对应数值）、week三个核心字段。
2. 透视（Pivot）：将week列的1-7值转为列，按Stats分组展示各周的对应数值。

具体SQL代码 #

MySQL版本（无原生透视函数，用条件聚合实现）

SELECT
  Stats,
  ROUND(MAX(CASE WHEN week = 1 THEN Value END), 2) AS Week1,
  ROUND(MAX(CASE WHEN week = 2 THEN Value END), 2) AS Week2,
  ROUND(MAX(CASE WHEN week = 3 THEN Value END), 2) AS Week3,
  ROUND(MAX(CASE WHEN week = 4 THEN Value END), 2) AS Week4,
  ROUND(MAX(CASE WHEN week = 5 THEN Value END), 2) AS Week5,
  ROUND(MAX(CASE WHEN week = 6 THEN Value END), 2) AS Week6,
  ROUND(MAX(CASE WHEN week = 7 THEN Value END), 2) AS Week7
FROM (
  -- 第一步：逆透视，把统计列转成行
  SELECT week, 'Avg_pts' AS Stats, avg_pts AS Value FROM your_table
  UNION ALL
  SELECT week, 'avg_fgm' AS Stats, avg_fgm AS Value FROM your_table
  UNION ALL
  SELECT week, 'avg_fga' AS Stats, avg_fga AS Value FROM your_table
  UNION ALL
  SELECT week, 'pct' AS Stats, pct AS Value FROM your_table
  UNION ALL
  SELECT week, 'avg_fg3m' AS Stats, avg_fg3m AS Value FROM your_table
) AS unpivoted
WHERE week BETWEEN 1 AND 7
GROUP BY Stats
ORDER BY 
  CASE Stats 
    WHEN 'Avg_pts' THEN 1
    WHEN 'avg_fgm' THEN 2
    WHEN 'avg_fga' THEN 3
    WHEN 'pct' THEN 4
    WHEN 'avg_fg3m' THEN 5
  END;

SQL Server版本（用原生PIVOT函数）

SELECT
  Stats,
  ROUND(Week1, 2) AS Week1,
  ROUND(Week2, 2) AS Week2,
  ROUND(Week3, 2) AS Week3,
  ROUND(Week4, 2) AS Week4,
  ROUND(Week5, 2) AS Week5,
  ROUND(Week6, 2) AS Week6,
  ROUND(Week7, 2) AS Week7
FROM (
  SELECT week, Stats, Value
  FROM your_table
  UNPIVOT (
    Value FOR Stats IN (avg_pts, avg_fgm, avg_fga, pct, avg_fg3m)
  ) AS unpivoted
) AS src
PIVOT (
  MAX(Value) FOR week IN ([1], [2], [3], [4], [5], [6], [7])
) AS pivoted
ORDER BY 
  CASE Stats 
    WHEN 'avg_pts' THEN 1
    WHEN 'avg_fgm' THEN 2
    WHEN 'avg_fga' THEN 3
    WHEN 'pct' THEN 4
    WHEN 'avg_fg3m' THEN 5
  END;

注意事项 #

• 将代码中的your_table替换为你的实际表名。
• ROUND(...,2)用于保留两位小数，和你期望的输出格式匹配，不需要可直接删除。
• ORDER BY子句用于固定统计项的展示顺序，可根据需求调整排序逻辑。

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