Transpose Rows to Column

Database
N/A

Transpose Rows to Column

Hallo Everyone

I have question on transposing a set of columns into rows.

For example,








C1 C2 C3 COUNT C5 C6 C7 C8 C9C C10 C11 C12 C13 C14 C15 C16
1 1 1 1 1 A B C                
2 2 2 2 6 U J H 10 Y S W        
3 3 3 3 5 V N M 3 Q P E 8 F D K

Consider 1 row where the count is 1 hence the transposed table has one row

Consider 2 row where the count is 2 hence the transposed table has 2 rows and henceforth




C1 C2 C3 C4 C5 C6 C7
1 1 1 1 A B C
2 2 2 6 U J H
2 2 2 10 Y S W
3 3 3 5 V N M
3 3 3 3 Q P E
3 3 3 8 F D K

I tried to select records separately based on COUNT column and then union all the counts.

But I am still facing, request you all to help me on this.

Thanks

Tags (1)
2 REPLIES
N/A

Re: Transpose Rows to Column

I think this will work...

select s.c1,

s.c2,

s.c3,

case

when c.id = 1 then c5

when c.id = 2 then c9

when c.id = 3 then c13

end as c4,

case

when c.id = 1 then c6

when c.id = 2 then c10

when c.id = 3 then c14

end as c5,

case

when c.id = 1 then c7

when c.id = 2 then c11

when c.id = 3 then c15

end as c6,

case

when c.id = 1 then c8

when c.id = 2 then c12

when c.id = 3 then c16

end as c7

from tabS s

cross join

(select calendar_date - current_date as id from sys_calendar.calendar where id between 1 and 3) c

where c.id <= s.count;

N/A

Re: Transpose Rows to Column

THANKS ALOT, IT WORKED LIKE MAGIC :)