oracle sql在一列中获取两个或多个值

r1zhe5dt 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(400)

create table emp(
emp_id number primary key,
emp varchar2(10),
emp_date date,
deptno number
)

Insert into emp (emp_id, emp,emp_date,deptno) values (1,'Ronaldo','01.01.2021.',1);
Insert into emp (emp_id, emp,emp_date,deptno) values (2,'Ronaldo','02.01.2021.',1);
Insert into emp (emp_id, emp,emp_date,deptno) values (3,'Ronaldo','03.01.2021.',1);
Insert into emp (emp_id, emp,emp_date,deptno) values (4,'Messi','01.01.2021.',1);
Insert into emp (emp_id, emp,emp_date,deptno) values (5,'Messi','02.01.2021.',2);
Insert into emp (emp_id, emp,emp_date,deptno) values (6,'Ronaldo','04.01.2021.',2);
Insert into emp (emp_id, emp,emp_date,deptno) values (7,'Messi','04.01.2021.',2);

             01.01.2021       |   02.01.2021      |   03.01.2021  |     04.01.2021 
------------------------------------------------------------------------------------------
deptno 1     Ronaldo, Messi       Ronaldo, Messi      Ronaldo

deptno 2     null                 Messi               null              Ronaldo, Messi

需要在上表中显示结果。

sql oracle

来源：https://stackoverflow.com/questions/67279023/oracle-sql-get-two-or-more-value-in-one-column

1条答案

按热度按时间

sd2nnvve1#

你可以试着用 listagg 与 CASE WHEN 查询1：

select deptno,
       listagg((CASE WHEN emp_date = '01.01.2021.' Then emp END),',') within group( order by emp_id ) "01.01.2021.",
       listagg((CASE WHEN emp_date = '02.01.2021.' Then emp END),',') within group( order by emp_id ) "02.01.2021.",
       listagg((CASE WHEN emp_date = '03.01.2021.' Then emp END),',') within group( order by emp_id ) "03.01.2021.", 
       listagg((CASE WHEN emp_date = '04.01.2021.' Then emp END),',') within group( order by emp_id ) "04.01.2021."
from emp 
group by deptno

结果：

| DEPTNO |   01.01.2021. | 02.01.2021. | 03.01.2021. |   04.01.2021. |
|--------|---------------|-------------|-------------|---------------|
|      1 | Ronaldo,Messi |     Ronaldo |     Ronaldo |        (null) |
|      2 |        (null) |       Messi |      (null) | Ronaldo,Messi |

赞(0）回复(0）举报 2021-07-26

我来回答

oracle sql在一列中获取两个或多个值

1条答案

相关问题

热门标签

最新问答