A表:
ID DTIME NUM
--------- -------- ---
101 20190201 10
101 20190202 20
101 20190203 50
101 20190203 20
102 20190201 20
102 20190202 30
102 20190203 60
103 20190201 60
103 20190203 90
select * from test_a pivot (
sum(num) for dtime in (20190201,20190202,20190203)
);
ID 20190201 20190202 20190203
---------- ---------- ---------- ----------
101 10 20 70
102 20 30 60
103 60 90
PIVOT语法
SELECT ...
FROM ...
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
说明:
pivot_clause:定义将要进行聚合操作的列;
pivot_for_clause:定义将要进行分组和转换的列;
pivot_in_clause:定义在pivot_for_clause中对列的过滤,对于pivot_in_clause中每一个值进行,并转换为单个列。
Sselect *from (select sal,deptno,job from emp)
pivot (
sum(sal)
for deptno in(10,20,30,40)
);
JOB 10 20