Formatting the output of SQL query to display multiple row in a single row

Question

I am trying to restructure the way the data is displayed

I have a table with the following structure

ID  Key         Value
1   OrgName     Google
1   email       John.Smith@gmail.com
1   Status      active
1   givenName   John
1   sn          Smith
1   userName    JSmith
2   OrgName     Yahoo
2   email       Paul.Jonesh@yahoo.com
2   Status      active
2   givenName   Paul    
2   sn          Jones
2   userName    PJones

How can I write an SQL query that will return it in this format

ID  givenName   sn
1   John        Smith
2   Paul        Jones

score 1 · Accepted Answer · answered Jul 26 '22 at 05:18

Aggregate, conditionally (is one option):

Sample data:

SQL> with test (id, key, value) as
  2    (select 1, 'OrgName'  , 'Google' from dual union all
  3     select 1, 'givenName', 'John'   from dual union all
  4     select 1, 'sn'       , 'Smith'  from dual union all
  5     select 2, 'OrgName'  , 'Yahoo'  from dual union all
  6     select 2, 'givenName', 'Paul'   from dual union all
  7     select 2, 'sn'       , 'Jones'  from dual
  8    )

Query begins here:

  9  select id,
 10    max(case when key = 'givenName' then value end) as givenName,
 11    max(case when key = 'sn'        then value end) as sn
 12  from test
 13  group by id;

        ID GIVENN SN
---------- ------ ------
         1 John   Smith
         2 Paul   Jones

SQL>

score 1 · Answer 2 · answered Jul 26 '22 at 05:31

Pivot functionality in Oracle can be used here. The query will look like

select * from (
  select key, id, value from test
) pivot (
  max(value) for key in ('OrgName', 'email', 'Status', 'givenName', 'sn', 'userName')
);

Similar question was answered here using pivot. It contains more details and explanation. Hope it helps!

Formatting the output of SQL query to display multiple row in a single row

2 Answers2