数据库oracle函数--结果集行转列pivot

结果集行转列函数pivot

语法格式

select * from t pivot(聚合函数 for 需要转为列的字段名 in(需要转为列的字段值))

实现功能

实现将指定字段的字段值转换为列的效果

示例目的：得到每条值班记录的各值班类型人员

字段说明：
main_id-值班日志id，duty_type-值班类型，employees-值班人员

1、未使用行转列函数
SELECT main_id,duty_type,
LISTAGG(person_duty, ‘,’) WITHIN GROUP (ORDER BY person_duty) AS employees
FROM tab
GROUP BY main_id,duty_type
结果：

2、使用行转列函数
SELECT * from (
SELECT duty_type,main_id,LISTAGG(person_duty, ‘,’) WITHIN GROUP (ORDER BY person_duty) AS employees
FROM tab
GROUP BY main_id,duty_type) t PIVOT (max(employees) FOR duty_type IN (‘1’ as employees1,‘2’ as employees2,‘3’ as employees3,‘4’ as employees4))

说明：不管转换的结果是不是由单表查出的，select后面都是跟的*