SQL Pivot数据列转行实现失败：结果异常原因排查求助

算力阿峰

2026-7-8

问题描述

原始数据：

groupid	sessnum	notes
QRgy6FhjgDVIPDt	0	notes overview
QRgy6FhjgDVIPDt	1	note s1
QRgy6FhjgDVIPDt	2	note s2
QRgy6FhjgDVIPDt	3	note s3
QRgy6FhjgDVIPDt	4	note s4
QRgy6FhjgDVIPDt	5	note s5
QRgy6FhjgDVIPDt	6	note s6
QRgy6FhjgDVIPDt	7	note s7
QRgy6FhjgDVIPDt	8	note s8
QRgy6FhjgDVIPDt	9	note s9
QRgy6FhjgDVIPDt	10	note s10

期望输出：

groupid	S1	S2	S3	S4	S5	S6	S7	S8	S9	S10	OVER
QRgy6FhjgDVIPDt	note s1	note s2	note s3	note s4	note s5	note s6	note s7	note s8	note s9	note s10	notes overview

当前使用的SQL：

SELECT groupid,
if(sessnum=1,notes,"") as S1,
if(sessnum=2,notes,"") as S2,
if(sessnum=3,notes,"") as S3,
if(sessnum=4,notes,"") as S4,
if(sessnum=5,notes,"") as S5,
if(sessnum=6,notes,"") as S6,
if(sessnum=7,notes,"") as S7,
if(sessnum=8,notes,"") as S8,
if(sessnum=9,notes,"") as S9,
if(sessnum=10,notes,"") as S10,
if(sessnum=0,notes,"") as OVER
FROM `grpnotes`
group by groupid

实际返回结果：

groupid	S1	S2	S3	S4	S5	S6	S7	S8	S9	S10	OVER
QRgy6FhjgDVIPDt	-	-	-	-	-	-	-	-	-	-	notes overview

问题分析与解决

你的SQL核心问题是未配合聚合函数使用GROUP BY：直接按groupid分组后，数据库只会返回每组的第一条匹配记录（这里恰好是sessnum=0的行），导致其他S1-S10字段都为空值。

要实现行转列的需求，需要用MAX()（或MIN()，因为每个sessnum对应唯一的notes值）聚合每个条件下的notes，确保分组后能拿到对应列的正确内容：

SELECT groupid,
MAX(if(sessnum=1,notes,"")) as S1,
MAX(if(sessnum=2,notes,"")) as S2,
MAX(if(sessnum=3,notes,"")) as S3,
MAX(if(sessnum=4,notes,"")) as S4,
MAX(if(sessnum=5,notes,"")) as S5,
MAX(if(sessnum=6,notes,"")) as S6,
MAX(if(sessnum=7,notes,"")) as S7,
MAX(if(sessnum=8,notes,"")) as S8,
MAX(if(sessnum=9,notes,"")) as S9,
MAX(if(sessnum=10,notes,"")) as S10,
MAX(if(sessnum=0,notes,"")) as OVER
FROM `grpnotes`
GROUP BY groupid