All Forums Database
marctd 4 posts Joined 02/12
08 Feb 2012
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:
ulrich 816 posts Joined 09/09
08 Feb 2012

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;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

marctd 4 posts Joined 02/12
09 Feb 2012

THANKS ALOT, IT WORKED LIKE MAGIC :)

You must sign in to leave a comment.