asked on

how to i convert column stored values to comma separated delimited rows

Hi experts,

I have a table ems_resp. the columns are
ems_id ems_qstn ems_resp
1000 address 123,halsted rd
1000 city detroit
1000 state michigan
1000 country USA
1001 address 789,valley rd
1001 city albany
1001 state new york
1001 country USA

i have another table entd_addr i need ti insert the above records in this fashion The entd_addr column is comma separated values. In the below table entd_id is the primary key.So, only one values.Its direct insert from ems_resp table to ents_addr table

entd_id entd_addr
1000 123,halsted rd,detroit, michigan, USA
1001 789,valley rd, albany,new york, USA

Zopilote

select e1.ems_id,
e1.ems_resp||','||e2.ems_resp||','||e3.ems_resp||','||e4.ems_resp
from ems_resp e1,
ems_resp e2,
ems_resp e3,
ems_resp e4
where e1.ems_qstn= 'address'
and e2.ems_id(+) = e1.ems_id
and e2.ems_qstn (+)= 'city'
and e3.ems_id(+) = e1.ems_id
and e3.ems_qstn(+) = 'state'
and e4.ems_id(+) = e1.ems_id
and e4.ems_qstn(+) = 'country'

Z;D

MikeOM_DBA

Try:

Insert Into entd_addr
Select Ems_Id, Max(Address)||', '||Max(City)||', '||Max(State||', '||Max(Country) Entd_Addr
From Ems_Resp Group By Ems_Id;

JacekMycha

I assume that ems_qstn can store only 4 values: 'address', 'city', 'state', 'country'.
SELECT
MAX(DECODE(ems_qstn, 'address', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'city', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'state', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'country', ems_resp, NULL))
FROM ems_resp
GROUP BY ems_id;

It's a clasic tranformation.

JacekMycha

vkchaitanya1

ASKER

Hi jackeymycha

Its working fine. But some of my data has some responses missing.for example
is my actual data
1001 address 789,valley rd
1001 city albany
1001 state new york
1001 country USA
If i have data like
1001 city albany
1001 state new york
then
the output i am getting is
entd_id ems_addr
1001 ,albany,newyork,

cant i avoid the front and back commas

jrb1

SELECT SUBSTR(
MAX(DECODE(ems_qstn, 'address', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'city', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'state', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'country', ems_resp, NULL)),
2,
LENGTH(MAX(DECODE(ems_qstn, 'address', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'city', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'state', ems_resp, NULL))||','||
MAX(DECODE(ems_qstn, 'country', ems_resp, NULL)))-2)
FROM ems_resp
GROUP BY ems_id;

ASKER CERTIFIED SOLUTION

sathyagiri

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zopilote

Did you try moving the commas inside?

SELECT
MAX(DECODE(ems_qstn, 'address', ems_resp||',', NULL))||
MAX(DECODE(ems_qstn, 'city', ems_resp||',', NULL))||
MAX(DECODE(ems_qstn, 'state', ems_resp||',', NULL))||
MAX(DECODE(ems_qstn, 'country', ems_resp, NULL))
FROM ems_resp
GROUP BY ems_id;

SOLUTION

Zopilote

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

MohanKNair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Computer101

Forced accept.

Computer101
EE Admin