You Asked
Hello,
I need to create a list of distinct values.
Here's my test case:
create table t(id number,dt date,txt varchar2(1));
alter table t add constraint t_pk primary key(id);
create sequence t_seq;
insert into t values(t_seq.nextval,trunc(sysdate)-5,'a');
insert into t values(t_seq.nextval,trunc(sysdate)-5,'b');
insert into t values(t_seq.nextval,trunc(sysdate)-5,'c');
insert into t values(t_seq.nextval,trunc(sysdate)-5,'b');
insert into t values(t_seq.nextval,trunc(sysdate)-5,'a');
insert into t values(t_seq.nextval,trunc(sysdate)-4,'x');
insert into t values(t_seq.nextval,trunc(sysdate)-4,'b');
insert into t values(t_seq.nextval,trunc(sysdate)-4,'b');
insert into t values(t_seq.nextval,trunc(sysdate)-4,'a');
insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
insert into t values(t_seq.nextval,trunc(sysdate)-3,'b');
insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
LISTAGG does not work for me: it gives duplicates and for real data I get ORA-01489: result of string concatenation is too long.
select distinct dt,listagg(txt,',') within group (order by txt) over(partition by dt) txt_list
from t;
DT TXT_LIST
------------ ----------
26-OCT-16 a,a,b,b,c
28-OCT-16 a,a,a,b
27-OCT-16 a,b,b,x
Is there way to get result like this:
DT TXT_LIST
------------ ----------
26-OCT-16 a,b,c
28-OCT-16 a,b
27-OCT-16 a,b,x
using only SQL? I work on a migration task and PL/SQL is not an option for me.
Thank you,
Yelena
and Connor said...
You can extract the distinct values first, and then use that
SQL> drop table t purge;
Table dropped.
SQL>
SQL> create table t(id number,dt date,txt varchar2(1));
Table created.
SQL> alter table t add constraint t_pk primary key(id);
Table altered.
SQL> create sequence t_seq;
create sequence t_seq
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-5,'a');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-5,'b');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-5,'c');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-5,'b');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-5,'a');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-4,'x');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-4,'b');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-4,'b');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-4,'a');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-3,'b');
1 row created.
SQL> insert into t values(t_seq.nextval,trunc(sysdate)-3,'a');
1 row created.
SQL>
SQL> col x format a30
SQL> select dt, listagg(txt,',') within group ( order by txt) x
2 from t
3 group by dt
4 order by dt;
DT X
--------- ------------------------------
27-OCT-16 a,a,b,b,c
28-OCT-16 a,b,b,x
29-OCT-16 a,a,a,b
SQL>
SQL> with new_t as
2 ( select distinct dt,txt from t )
3 select dt, listagg(txt,',') within group ( order by txt) x
4 from new_t
5 group by dt
6 order by dt;
DT X
--------- ------------------------------
27-OCT-16 a,b,c
28-OCT-16 a,b,x
29-OCT-16 a,b
SQL>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment