Table pivoting

Database
Enthusiast

Table pivoting

Hi everyone,

I have a requirement where I need to pivot a table (columns to rows). An example is as below..notice that the table has 900 columns. Your help is very much appreciated.

col1 col2 col3 col4 col5     .....    col900

abc  1      2     3     4                       5

def   3     4     5     6                       10

I need the output to be

abc  col2  1

abc  col3  2

abc  col4  4

abc  col5  4

...

abc  col900 10

def   col2  3

def   col3  4

def    col4 5

..

def   col900 10

2 REPLIES
Senior Supporter

Re: Table pivoting

first generate  two big case statements

select calendar_date - current_date as id,
'when c.id = ' !! trim(cast(id as varchar(20))) !! ' then ''col' !! trim(cast(id as varchar(20)))!! ''''
from sys_calendar.calendar
where id between 2 and 900
order by 1;

select calendar_date - current_date as id,
'when c.id = ' !! trim(cast(id as varchar(20))) !! ' then col' !! trim(cast(id as varchar(20)))
from sys_calendar.calendar
where id between 2 and 900
order by 1;

Paste the results (only second colum of result set) into the following SQL

select t.col1,
case
<<<first query result>>>
end as colname,
case
<<<second query result>>>
end as colname,
from yourtable as t
cross join
(select current_date - current_Date as id from sys_calendar.calendar where id between 2 and 900) as c
;

Enthusiast

Re: Table pivoting

Thank you so much Ulrich. This is a very simple solution. You've made it look much simpler than it is. I was concentrating more from a "recursive" perspective.

A small correction, the statement after the cross join, it is

(select calendar_date - current_Date as id from sys_calendar.calendar where id between 2 and 900) as c

Thank you very much.