SELECT: UNPIVOT¶
说明¶
将查询结果由列转行
参数¶
unpivot_column_clause
作为转换后新增列，通常用来表示unpivot_for_clause列对应的值
unpivot_for_clause
作为转换后新增列，表示一个范畴或者类别、目录。其值取自于unpivot_in_clause里的串
unpivot_in_clause
包含了需要被转置的列，取自于原结果集的列
constant- 表示常量，别名必须是常量
注解
1)univot 只对前面临近的结果集进行列转行，结果集可以是一个表，一个子查询，或者函数。可参考 select 用法里的 table_ref，且如果是函数的情况，只能是 table() 函数。但神通目前暂不支持对函数结果集进行 unpivot 转换
2)每个部分都需要保证最终转换后的列不能有重名，产生歧义。
3)unpivot_column_clause 的个数与别名多少无关，只跟 unpivot_in_clause 值个数有关，二者个数必须对应一致。例如：select course,teacher from stu_score unpivot ((score,tname,dept) for (course,teacher) in((Chinese,C_teacher,dept1)as ('a1','a2'),(Math,M_teacher,dept2)as ('b1','b2')));
4)unpivot_for_clause 的个数只与 unpivot_in_clause 的别名个数有关，当没有别名时默认就是in的串连接。例如：select course,teacher from stu_score unpivot ((score,tname,dept) for (course,teacher) in((Chinese,C_teacher,dept1)as ('a1','a2'),(English,E_teacher,dept3))));
5)unpivot_in_clause 必须是原结果集的列，可以来自多个不同的表。
6)unpivot_in_clause 里面元素的类型要一致，必须有统一的父类型，比如都是数字类型、字符串类型等。最终UNPIVOT转换后的结果会选择父类型作为新增列的类型。
7)include nulls 和 exclude nulls 针对的是 unpivot_in_clause 的结果是否可以允许空记录。
示例¶
示例1: 结果是否含空测试
-- 清理环境
drop table t;
create table t(id int,name varchar(100),Chinese int,Math int,English int,C_teacher varchar(20),M_teacher varchar(20),E_teacher varchar(20));
insert into t values(1,'zs',99,88,77,'t1','t2','t3');
insert into t values(2,'ls',66,55,null,'t1','t2','t3');
select * from t unpivot include nulls(score for course in(Chinese,Math,English)) order by id,course;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |COURSE(varchar) |SCORE(int) |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |CHINESE |99 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |ENGLISH |77 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |MATH |88 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |CHINESE |66 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |ENGLISH |null |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |MATH |55 |
总数目：6
select * from t unpivot exclude nulls(score for course in(Chinese,Math,English)) order by id,course;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |COURSE(varchar) |SCORE(int) |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |CHINESE |99 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |ENGLISH |77 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |MATH |88 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |CHINESE |66 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |MATH |55 |
总数目：5
示例2: 转置列unpivot_column_clause测试
select * from t unpivot (score for course in(Chinese as 'Chinese',Math as 'Math',English as 'English'))order by id,course;
ID(int) |NAME(varchar) |C_TEACHER(varchar) |M_TEACHER(varchar) |E_TEACHER(varchar) |COURSE(varchar) |SCORE(int) |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |Chinese |99 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |English |77 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 |zs |t1 |t2 |t3 |Math |88 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |Chinese |66 |
---------------------------------------------------------------------------------------------------------------------------------------------
2 |ls |t1 |t2 |t3 |Math |55 |
总数目：5
select * from t unpivot ((score,tname) for course in((Chinese,C_teacher)as 'Chinese',(Math,M_teacher)as 'Math',(English,E_teacher)as 'English'))order by id,course;
ID(int) |NAME(varchar) |COURSE(varchar) |SCORE(int) |TNAME(varchar) |
-----------------------------------------------------------------------------------------
1 |zs |Chinese |99 |t1 |
-----------------------------------------------------------------------------------------
1 |zs |English |77 |t3 |
-----------------------------------------------------------------------------------------
1 |zs |Math |88 |t2 |
-----------------------------------------------------------------------------------------
2 |ls |Chinese |66 |t1 |
-----------------------------------------------------------------------------------------
2 |ls |English |null |t3 |
-----------------------------------------------------------------------------------------
2 |ls |Math |55 |t2 |
总数目：6
示例3: unpivot_in_clause和unpivot_for_clause测试
select * from t unpivot ((score,tname) for (course,teacher) in((Chinese ,C_teacher)as('Chinese','Chinese_teacher'),(Math ,M_teacher)as('Math','Math_teacher'),(English,E_teacher)as('English','English_teacher')))order by id,teacher;
ID(int) |NAME(varchar) |COURSE(varchar) |TEACHER(varchar) |SCORE(int) |TNAME(varchar) |
---------------------------------------------------------------------------------------------------------------
1 |zs |Chinese |Chinese_teacher |99 |t1 |
---------------------------------------------------------------------------------------------------------------
1 |zs |English |English_teacher |77 |t3 |
---------------------------------------------------------------------------------------------------------------
1 |zs |Math |Math_teacher |88 |t2 |
---------------------------------------------------------------------------------------------------------------
2 |ls |Chinese |Chinese_teacher |66 |t1 |
---------------------------------------------------------------------------------------------------------------
2 |ls |English |English_teacher |null |t3 |
---------------------------------------------------------------------------------------------------------------
2 |ls |Math |Math_teacher |55 |t2 |
总数目：6
drop table t;