Solved

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

Posted on 2006-06-23
11
1,866 Views
Last Modified: 2010-08-05
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

0
Comment
Question by:vkchaitanya1
11 Comments
 
LVL 5

Expert Comment

by:Zopilote
ID: 16970334
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
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16970358

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;

0
 
LVL 3

Expert Comment

by:JacekMycha
ID: 16971117
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
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:vkchaitanya1
ID: 16971477
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 16971543
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;
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 134 total points
ID: 16972772
This might not be the best way to doit. Anyways here it goes.

Create a function like below

create or replace function fn_join
 (
     p_cursor sys_refcursor,
     p_delimiter varchar2 := ','
 ) return varchar2
 is
     l_value   varchar2(32767);
     l_result  varchar2(32767);
 begin
     loop
         fetch p_cursor into l_value;
         exit when p_cursor%notfound;
         if l_result is not null then
             l_result := l_result || p_delimiter;
         end if;
         l_result := l_result || l_value;
     end loop;
     return l_result;
 end fn_join;

Then use

insert into entd_addr (select distinct ems_id, fn_join(cursor (select ems_resp from ems_resp a where
a.ems_id = b.ems_id) ) from ems_resp b)
0
 
LVL 5

Expert Comment

by:Zopilote
ID: 16972912
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;
0
 
LVL 5

Assisted Solution

by:Zopilote
Zopilote earned 133 total points
ID: 16972944
Sorry, this way...

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)
FROM ems_resp
GROUP BY ems_id;
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 133 total points
ID: 16977486
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20294555
Forced accept.

Computer101
EE Admin
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle report printing 2 pages in one page 2 69
Encryption Decryption in Oracle 12 121
null value 15 102
error in my cursor 5 32
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question