1.行转列(PIVOT)
现有学生-分数表 STUDENT_SCORE如下:
想看到每个学生语数外的整体分数情况,这时候可以应用行转列(PIVOT):
SELECT * FROM STUDENT_SCOREPIVOT(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_TYPEUNPIVOT(HISTORY_ID FOR PERIOD IN (PERIOD_01, PERIOD_02, PERIOD_03))WHERE ID = 3
查询结果为:
2>根据ID获取它所经历过的PERIOD的TYPE
SELECT TYPE FROM PERIOD_TYPEWHERE ID IN (SELECT HISTORY_ID FROM PERIOD_TYPEUNPIVOT(HISTORY_ID FOR PERIOD IN (PERIOD_01, PERIOD_02, PERIOD_03))WHERE ID = 3)
得到最终结果: