如何在Oracle SQL中将行转换为列

本文介绍了如何在Oracle SQL中将行转换为列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

对于一项要求，我必须创建一个查询以显示每周的员工时间表.查询是这样的:

For a requirement I have to create a query to show Employees Schedule per week. Query is like this:

select weekday, sched_hrs from table where emplid = '12345' and weekday_name= 1

此查询的输出如下:

Weekday | Sched_hrs -------------------- 1 | 7.6 1 | 7.6 1 | 7.6 1 | 7.6 1 | 7.6 1 | OFF 1 | OFF

我想要以下格式的输出:

I want the output in below format:

1 7.6 7.6 7.6 7.6 7.6 OFF OFF

如何实现?

推荐答案

如果您对串联列表没问题，请使用中引入的 LISTAGG .

If you are OK with concatenated list, then use LISTAGG which was introduced in Oracle 11g Release 2.

SELECT weekday, LISTAGG(Sched_hrs, ',') WITHIN GROUP (ORDER BY weekday) AS Sched_hrs FROM table WHERE emplid = '12345' AND weekday_name= 1 GROUP BY weekday;

例如

SQL> column employees format a50 SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees 2 FROM emp 3 GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD SQL>