Transpose columns to rows

Database
Enthusiast

Transpose columns to rows

Hi All,

         Here is scenario:

Cust   Act_cnt  Act1   Act2  Act3  Act4  Act5  ....... Act15

A          2          A01    A02   ?       ?      ?  ...............?

B          1          B01     ?      ?       ?      ?  ...............?

C          3          C01   C02   C03   ?      ?  ...............?

after transpose I would like to have result like:

Cust   Accnt

A        A01

A        A02

B        B01

C        C01

C        C02

C        C03

           Can I use recursive to transpose as acct_cnt is available? Please suggest an approach.

Regards

Chandrashekar

3 REPLIES
Senior Apprentice

Re: Transpose columns to rows

Hi Chandrashekar,

you should avoid recursion for stuff like that, it will result in huge spool usage.

There are two common solutions for:

select cust, act1 as accnt from tab where act1 is not null
union all
select cust, act2 as accnt from tab where act2 is not null
union all
select cust, act3 as accnt from tab where act3 is not null
union all
...

or a cross join to a table with integers like:

select cust, 
case x
when 1 then act1
when 2 then act2
when 3 then act3
...
end as accnt
from tab cross join
(select day_of_calendar as x from sys_calendar.calendar
where x between 1 and 15) as dt

Dieter

Enthusiast

Re: Transpose columns to rows

Thanks Dieter for the solution.

Re: Transpose columns to rows

Hi

I have a huge table in production when I am trying to do column to row transpose using above first method I am getting spool space error.

Can any one plz helpme to do to query tuning here.

Thank you in advance.