1.行转列(PIVOT)
现有 学生-分数表 STUDENT_SCORE 如下:
想看到每个学生语数外的整体分数情况,这时候可以应用行转列(PIVOT):
SELECT * FROM STUDENT_SCORE
PIVOT(
SUM(SCORE) FOR SUBJECT IN ('语文' AS CHINESE, '数学' AS MATH, '英语' AS ENGLISH)
)
查询结果为:
2.列转行(UNPIVOT)
现有 阶段-类型表 PERIOD_TYPE 如下:
每行记录能够追溯它已经进行过的PERIOD的ID(自关联),由此,我们现在假设一个需求,
现在去查ID为3的表记录,它所经历过的PERIOD的TYPE:
1>首先,可以查出它已经进行过的PERIOD的ID
SELECT PERIOD, HISTORY_ID FROM PERIOD_TYPE
UNPIVOT(
HISTORY_ID FOR PERIOD IN (PERIOD_01, PERIOD_02, PERIOD_03)
)
WHERE ID = 3
查询结果为:
2>根据ID获取它所经历过的PERIOD的TYPE
SELECT TYPE FROM PERIOD_TYPE
WHERE ID IN (
SELECT HISTORY_ID FROM PERIOD_TYPE
UNPIVOT(
HISTORY_ID FOR PERIOD IN (PERIOD_01, PERIOD_02, PERIOD_03)
)
WHERE ID = 3
)
得到最终结果: