Convert a set of columns into rows in ORACLE SQL

Question

I have a query that returns a table that looks somewhat like below.

In the image, I want the Input table to look like the Output table --

Essentially, I want all the data from columns to get transposed into rows. But I do not want to convert rows into columns.

I have seen solutions to transpose rows and columns but wanted to check if there is something easier out there that anyone knows of. Any help appreciated!

TIA

@Sujitmohanty30, it is going to be the columns [0][1][2][3][4] from the Input table that will get converted into rows. — kpanse, Sep 08 '20 at 17:47
Yes, it worked. I had to make some modifications, but I got the idea. Thanks! — kpanse, Sep 09 '20 at 18:44

Sujitmohanty30 · Accepted Answer · 2020-09-08T18:04:36.843

Like you already know to convert rows to columns PIVOT is required , similarly to convert columns to rows we need UNPIVOT.

As there is no specific table with column names specified I have used WITH clause to create a temporary table to demonstrate. Here is link to know more about the clauses used in UNPIVOT for your reference.

with table1
as
(
select 'Inbound' department, 50 hour0, 44 hour1, 29 hour2, 47 hour3, 17 hour4 from dual
union all
select 'Outbound', 6, 4, 10, 24, 39 from dual
union all
select 'Returns', 3, 1, 39, 43, 35 from dual
)
select *
  from table1 t1
unpivot (value for hour in (hour0 as '0',hour1 as '1',hour2 as '2',hour3 as '3',hour4 as '4'))

score 0 · Answer 2 · answered Sep 08 '20 at 17:58

In Oracle, I would recommend a lateral join to unpivot the table. Assuming that that the columns are called hour0, hour1, hour2, ..., this would phrase as:

select t.department, x.*
from mytable t
cross apply (
    select 0 as hour, t.hour0 as value from dual
    union all select 1, t.hour1 from dual
    union all select 2, t.hour2 from dual
    union all ...
) x

Convert a set of columns into rows in ORACLE SQL

2 Answers2