oralce函数 PIVOT (聚合函数（列）for in（）) 行转列

PIVOT 语法为

SELECT <非透视的列>, [第一个透视的列],...[最后一个透视的列] FROM (<生成数据的 SELECT 查询>)  PIVOT (<聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列],... [最后一个透视的列]) ) AS <透视表的别名> <可选的 ORDER BY 子句>

 

看下面SQL执行结果为

SELECT TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
PROJECT_TYPE,COUNT(*) SUM,
NULL COUNT_NUM from  
PROJECT_DECLARATION
GROUP BY TO_CHAR(CREATE_TIME,'yyyy-MM'),PROJECT_TYPE

我们如果想行转列

SQL如下

SELECT * FROM 
(
SELECT TO_CHAR(CREATE_TIME,'yyyy-MM') CREATE_TIME,
PROJECT_TYPE,COUNT(*) SUM,
NULL COUNT_NUM from  
PROJECT_DECLARATION
GROUP BY TO_CHAR(CREATE_TIME,'yyyy-MM'),PROJECT_TYPE
)
PIVOT (SUM(SUM) NUM FOR PROJECT_TYPE IN ('projectType1' TYPE1,  'projectType2' TYPE2,'projectType3' TYPE3))
ORDER BY CREATE_TIME

结果为