CODEWITHSUNDEEP
sqloracle-databaseunpivot
kpanse
kpanse

Reputation: 31

Convert a set of columns into rows in ORACLE SQL

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

enter image description here

Upvotes: 2

Views: 11231

Answers (2)

Sujitmohanty30
Sujitmohanty30

Reputation: 3316

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'))

Upvotes: 1

GMB
GMB

Reputation: 222402

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

Upvotes: 0

Related Questions