You Asked
Hi Tom,
Is it possible to pivot only records with duplicate values in a given column.
Here is the scenario
create table test(id number, cname varchar2(10), cvalue varchar2(10))
insert into test values(1, 'a', '10');
insert into test values(2, 'b', '20');
insert into test values(3, 'D', '45');
insert into test values(4, 'D', '50');
insert into test values(5, 'e', '70');
select * from test
id cname cvalue
--- ----- -------
1 a 10
2 b 20
3 D 45 ***
4 D 50 ***
5 e 70
Is it possible to write a query to pivot only the duplicate records based on cname column
cname cvalue
a 10
b 20
D 45,50 =========================> value D repeated twice and hence is pivoted to get only one record.
e 70
Thanks in advance.
and Connor said...
Yes, a simple LISTAGG will do the trick
SQL> create table test(id number, cname varchar2(10), cvalue varchar2(10));
Table created.
SQL>
SQL>
SQL>
SQL> insert into test values(1, 'a', '10');
1 row created.
SQL>
SQL> insert into test values(2, 'b', '20');
1 row created.
SQL>
SQL> insert into test values(3, 'D', '45');
1 row created.
SQL>
SQL> insert into test values(4, 'D', '50');
1 row created.
SQL>
SQL> insert into test values(5, 'e', '70');
1 row created.
SQL>
SQL> select cname, listagg(cvalue,',') within group ( order by id ) as dup
2 from test
3 group by cname
4 order by lower(cname);
CNAME DUP
---------- ------------------------------
a 10
b 20
D 45,50
e 70
Is this answer out of date? If it is, please let us know via a Comment