Oracle - how to select multiple rows as 1 row with columns

Question

I am using Oracle 12c and have data coming back with multiple rows that I would like to switch to a single row select statement that has headers describing the data. The twist here is that the data column is a CLOB.

Here is an example (in reality, this would be a dozen rows):

select ID, description, data from dual


|---------------------|------------------|------------------|
|          ID         |     Description  |       Data       |
|---------------------|------------------|------------------|
|          1          |     DescriptionA |      TestA       |
|---------------------|------------------|------------------|
|          2          |     DescriptionB |      TestB       |
|---------------------|------------------|------------------|

I would like it to look like this instead:

|---------------------|------------------|
|   DescriptionA      |    DescriptionB  |
|---------------------|------------------|
|          TestA      |    TestB         |
|---------------------|------------------|

Any ideas are greatly appreciated!

Thank you!

Possible duplicate of [oracle sql pivot query table](https://stackoverflow.com/questions/22815594/oracle-sql-pivot-query-table) — Ken White, Sep 11 '18 at 01:22
Possible duplicate of https://stackoverflow.com/questions/22587600/how-to-select-column-value-as-column-name-with-conditions-in-sql-table — Stidgeon, Sep 11 '18 at 01:25

score 1 · Answer 1 · answered Sep 11 '18 at 02:22

You can use case when

with t(ID, Description,Data) as
    (
      select  1, 'DescriptionA','TestA' from dual
      union all
      select 2, 'DescriptionB','TestB' from dual
    )
    select max( case when Data='TestA' then Data end) as DescriptionA,
    max(case when Data='TestB' then Data end) as DescriptionB from t

DESCRIPTIONA    DESCRIPTIONB
TestA           TestB

score 1 · Answer 2 · answered Sep 11 '18 at 16:11

Here is also an option, If you want dynamic values instead of hard-coded for ID column then use dynamic query.

  SELECT  MAX(DECODE(T.ID, 1, T.TE)) AS DES1,
          MAX(DECODE(T.ID, 2, T.TE)) AS DES2
          FROM (SELECT 1 as id, 'DescriptionA' AS DES, 'TestA' AS TE FROM DUAL
UNION ALL
  SELECT 2 as id, 'DescriptionB' AS DES, 'TestB' AS TE FROM DUAL)T

Oracle - how to select multiple rows as 1 row with columns

2 Answers2