25M表T有5个字段emid,c1,c2,c3,dt分别代表工号，字段1，字段2，字段3和日期，数据如左图，我想按得到右边的结果，即：同一个工号，如果同一个日期有两行或三行则“压缩”成一行，怎么弄？
谢谢！
drop table t;
create table t (emid varchar(20),c1 varchar(20),c2 varchar(20),c3 varchar(20),dt date);
insert into t values('A','V1',null,null,to_date('2024-08-25','yyyy-mm-dd'));
insert into t values('A',null,'V2',null,to_date('2024-08-25','yyyy-mm-dd'));
insert into t values('A',null,null,'V3',to_date('2024-08-25','yyyy-mm-dd'));
insert into t values('A',null,'V4',null,to_date('2024-08-27','yyyy-mm-dd'));
insert into t values('A','V5',null,null,to_date('2024-08-28','yyyy-mm-dd'));
commit;
select * from t;
select emid,max(c1) c1,max(c2) c2,max(c3) c3,dt from t group by emid,dt order by emid,dt;
评论
有用 1
SELECT
emid,
dt,
LISTAGG(c1, ', ') WITHIN GROUP (ORDER BY ROWID) AS c1_combined,
LISTAGG(c2, ', ') WITHIN GROUP (ORDER BY ROWID) AS c2_combined,
LISTAGG(c3, ', ') WITHIN GROUP (ORDER BY ROWID) AS c3_combined
FROM T
GROUP BY emid, dt;
评论 有用 0SELECT
Emid,LISTAGG(c1,',') WITHIN GROUP (ORDER BY C1) AS C1,
LISTAGG(c2,',') WITHIN GROUP (ORDER BY c2) AS C2,
LISTAGG(c3,',') WITHIN GROUP (ORDER BY c3) AS C3,
Dt
FROM t
GROUP BY Emid,dt;
评论 有用 0