Skip to Main Content
  • Questions
  • pivot records based on duplicate values in one column

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 17, 2017 - 9:00 pm UTC

Last updated: August 18, 2017 - 1:18 am UTC

Version: Oracle 12c

Viewed 1000+ times

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.