Mulitple Row values into a sinlge Column...

Database
l_k
N/A

Mulitple Row values into a sinlge Column...

Hi,

I have a scenario to combine all the column's value in a single column.For example,we have 5 columns like Pay_code1,Pay_code2,Pay_code3,Pay_code4 and Pay_code5.Each column will contain values and sometimes blank value.

I need an output of all the column's values in a single column without blank value.Below is the table which has the value as follows:

Record Paycode1 Paycode2 Paycode3 Paycode4 Paycode5
1 OVT REG CONS
2 REG FUNL
3 FUNL OVT OVT

The output should be in a single column like (blank values should be not displayed),

Paycode
OVT
REG
CONS
REG
FUNL
FUNL
OVT
OVT

This is urgent.i am expecting a valuable help from you.

Thanks

Lavakumar

2 REPLIES
N/A

Re: Mulitple Row values into a sinlge Column...

Two ways to achieve that result set:

select paycode1 from tab where paycode1 <> ''
union all
select paycode2 from tab where paycode2 <> ''
union all
...

or a Cross Join to a helper table:
select
case x
when 1 then PayCode1
when 2 then PayCode2
...
end as paycode
from tab cross join
(select day_of_calendar as x from sys_calendar.calendar
where x between 1 and 5) dt
where paycode <> ''

The Cross Join is probably more efficient

Dieter
l_k
N/A

Re: Mulitple Row values into a sinlge Column...

Dieter,
Thank you very much for your help...Cross join was really helpful to me...

Lavakumar