All Forums Database
td_admirer 36 posts Joined 07/12
02 Aug 2012
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

ulrich 816 posts Joined 09/09
02 Aug 2012

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
;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

td_admirer 36 posts Joined 07/12
02 Aug 2012

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.

You must sign in to leave a comment.