Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Merge multiple columns into single column?

user10989620Sep 9 2009 — edited Sep 9 2009
Hi,

I need to execute queries dynamically and return the values. I need to fetch the values of the queries in single column only by concatenating the multiple columns. I cannot use PL/SQL in my scenario.
is there any way to execute a query and the result will be fetched in single column (values of multiple columns needs to be concatenated)?

Thanks,
Raja.
This post has been answered by ravikumar.sv on Sep 9 2009
Jump to Answer

Comments

babaravi
hi,

do you mean this??
  1* select EMPNO||' '||ENAME||' '||JOB||' '||MGR||' '||HIREDATE||' '||SAL||' '||COMM||' '||DEPTNO||
SQL> /

MULTIPLE_COL
--------------------------------------------------------------------------------
100 JDF DIR  05-SEP-09 200 1000 10
7497 MILLER CLERK 7782 23-JAN-82 25000 195 35
7566 JONES MANAGER 7839 02-APR-81 3175 446.25 20
7654 RAR SALESMAN 7698 28-SEP-81 1450 1587.5 30
7698 BLAKE MANAGER 7839 01-MAY-81 3050 427.5 30
7782 CLARK MANAGER 7839 09-JUN-81 2650 367.5 10
7788 SCOTT ANALYST 7566 09-DEC-82 3200 450 20
7839 KING PRESIDENT  17-NOV-81 5200 8250 10
7844 TURNER SALESMAN 7698 08-SEP-81 1700 225 30
7876 ADAMS CLERK 7788 12-JAN-83 1300 165 20
7900 JAMES CLERK 7698 03-DEC-81 1150 85.5 30 xyz@xyz.com

MULTIPLE_COL
--------------------------------------------------------------------------------
7902 FORD ANALYST 7566 03-DEC-81 3200 450 20
8000 KINGBABA PRESIDENT  17-NOV-81 5200 8250 10
8001 TURNER RAV SALESMAN 8000 08-SEP-81 1700 450 30
1001 KITTU DOR  05-SEP-09 1200 100 40

15 rows selected.
Or

Mean this??
SQL> ed
Wrote file afiedt.buf

  1  With T As
  2     ( Select Level col1 From dual Connect By Level<=10
  3     )
  4     Select Max(SYS_CONNECT_BY_PATH(COL1||',',' ')) Multi_col
  5       From
  6     ( Select COL1, Lag(COL1) Over (Order By COL1) As Lag
  7             From   T  )
  8      Start With Lag Is Null
  9        Connect By
 10*    Prior col1 = LAG
SQL> /

MULTI_COL
--------------------------------------------------------------------------------
 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
Edited by: user291283 on Sep 8, 2009 10:10 PM
user10989620
Yes, but i cannot directly use the || symbols with the query columns.
but, i can use wrapper queries to my input query to achieve this functionality.

In my requirement, i cannot change the input query.
Can we do this with wrapper query or any other way to do this?
babaravi
hi,

can you tell me about "wrapper queries"?
what is it actually?

can you give some example with wrapper queries?? :D

Edited by: user291283 on Sep 8, 2009 10:26 PM
user10989620
Some times we cannot do some operations with single query.
in this case, Use of a covering query will help to perform the task,
like,

select xyz from
( select abc from abc)

Using this outer (wrapper) query, we can process the output of inner query as we need.

Anybody help me in my scenario pls...

Edited by: user10989620 on Sep 8, 2009 10:41 PM
brtk
Hi,

Create view with new definition of columns, like:
create view v1 as 
select EMPNO||' '||ENAME||' '||JOB as newcolumn
from emp;

select newcolumn from v1;
You may hide original table and create view the same as table if needed.

From v11.1 you may also add virtual column to table:
alter table emp add (     emp_comm_salry AS (ROUND(emp_base_salry*(1+emp_comm_pct/100), 2)) );
(not tested)


Bartek
user10989620
actually we cannot edit the query to append ||.
We will be passing the query as input and the component should return the values in single column rows.

This component is generic and there will be 1000+ queries it has to process.

I need something like SYS_CONNECT_BY_PATH which is doing the same operation in rows.
MichaelS
is there any way to execute a query and the result will be fetched in single column (values of multiple columns needs to be concatenated)?
SQL>  select trim(column_value) data 
  from xmltable('for $i in ROW return string-join($i/*," ")' passing dbms_xmlgen.getxmltype('select * from dept').extract('ROWSET/ROW'))
/
DATA                                              
--------------------------------------------------
10 ACCOUNTING NEW YORK                            
20 RESEARCH DALLAS                                
30 SALES CHICAGO                                  
40 OPERATIONS BOSTON

4 rows selected.
user10989620
This is the one I am expecting. Thank you!

However, I am getting the output as (OraXML) in TOAD
no rows selected in SQL*Plus.

should I check for any setups?
ravikumar.sv
 SELECT rtrim(extract(xmltype(REPLACE(column_value,'</',' </')),'/ROW/*/text()')) col
   FROM TABLE
  (SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
     FROM dual
  );
check this..may be it might work for you

edit: this is formatting the code which i sent you...so get the query extracted from Qoute Original to my post.

Ravi Kumar

Edited by: ravikumar.sv on Sep 9, 2009 12:52 PM
user10989620
Hi Ravi Kumar,

This is working cool for me...

there is no delimiters in between the values in output.
Please tell me how could i get it?
ravikumar.sv
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</','$$</')),'/ROW/*/text()'),'$$') col
   FROM TABLE
  (SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
     FROM dual
  );
check the $$ sign..that is where the delimiter is..

Ravi Kumar
user10989620
Actually the query was giving "missing right parenthesis" error.
i have change the part ",'</' </'))," to ",'</ </'))," then it worked.

I have added , as you said but, I am not getting it in output. Please help me.

SELECT rtrim(extract(xmltype(REPLACE(column_value,'</ , </')),'/ROW/*/text()'),',') col
FROM TABLE
(SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
FROM dual
);
ravikumar.sv
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</','$$</')),'/ROW/*/text()'),'$$') col
   FROM TABLE
  (SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
     FROM dual
  );
hi $$ is the delimiter in the above query which I added...
the output format is getting changed when i posted it. There is a bug or a special meaning for that format in this forum...
So first reply to my post and then click on "Quote Original" option in the editor...then you collect the correct sql..

Ravi Kumar
user10989620
ravikumar.sv wrote:
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</','$$</')),'/ROW/*/text()'),'$$') col
FROM TABLE
(SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
FROM dual
);
hi $$ is the delimiter in the above query which I added...
the output format is getting changed when i posted it. There is a bug or a special meaning for that format in this forum...
So first reply to my post and then click on "Quote Original" option in the editor...then you collect the correct sql..

Ravi Kumar
Thank you for guided me the procedure!
Please help me for the answer.
ravikumar.sv
what problem are you facing now??

Ravi Kumar
user10989620
I have replaced the $$ with comma (,) in the query. Still I am not getting the delimiter in the output.
ravikumar.sv
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</',',</')),'/ROW/*/text()'),',') col
   FROM TABLE
  (SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
     FROM dual
  );
now i added ',' as delimiter to the query...
it is working for me..

you need to change at two places inside replace function and at rtrim function.

If the error still persists...please post the query as well as error which you are using...

Ravi Kumar
MichaelS
no rows selected in SQL*Plus.
Do you have the DEPT table in your schema?

You can alos try with EMPLOYEES of course:
SQL> set pages 5000

SQL> select trim(column_value) data 
  from xmltable('for $i in ROW return string-join($i/*,",")' passing dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
/
DATA                                                                            
--------------------------------------------------------------------------------
100,Steven,King,SKING,515.123.4567,17.06.87,AD_PRES,24000,90                    
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21.09.89,AD_VP,17000,100,90             
102,Lex,De Haan,LDEHAAN,515.123.4569,13.01.93,AD_VP,17000,100,90                
103,Alexander,Hunold,AHUNOLD,590.423.4567,03.01.90,IT_PROG,9000,102,60          
104,Bruce,Ernst,BERNST,590.423.4568,21.05.91,IT_PROG,6000,103,60                
105,David,Austin,DAUSTIN,590.423.4569,25.06.97,IT_PROG,4800,103,60              
106,Valli,Pataballa,VPATABAL,590.423.4560,05.02.98,IT_PROG,4800,103,60          
107,Diana,Lorentz,DLORENTZ,590.423.5567,07.02.99,IT_PROG,4200,103,60            
108,Nancy,Greenberg,NGREENBE,515.124.4569,17.08.94,FI_MGR,12000,101,100         
109,Daniel,Faviet,DFAVIET,515.124.4169,16.08.94,FI_ACCOUNT,9000,108,100         
110,John,Chen,JCHEN,515.124.4269,28.09.97,FI_ACCOUNT,8200,108,100               
111,Ismael,Sciarra,ISCIARRA,515.124.4369,30.09.97,FI_ACCOUNT,7700,108,100       
112,Jose Manuel,Urman,JMURMAN,515.124.4469,07.03.98,FI_ACCOUNT,7800,108,100     
113,Luis,Popp,LPOPP,515.124.4567,07.12.99,FI_ACCOUNT,6900,108,100               
114,Den,Raphaely,DRAPHEAL,515.127.4561,07.12.94,PU_MAN,11000,100,30             
115,Alexander,Khoo,AKHOO,515.127.4562,18.05.95,PU_CLERK,3100,114,30             
116,Shelli,Baida,SBAIDA,515.127.4563,24.12.97,PU_CLERK,2900,114,30              
117,Sigal,Tobias,STOBIAS,515.127.4564,24.07.97,PU_CLERK,2800,114,30             
118,Guy,Himuro,GHIMURO,515.127.4565,15.11.98,PU_CLERK,2600,114,30               
119,Karen,Colmenares,KCOLMENA,515.127.4566,10.08.99,PU_CLERK,2500,114,30        
120,Matthew,Weiss,MWEISS,650.123.1234,18.07.96,ST_MAN,8000,100,50               
121,Adam,Fripp,AFRIPP,650.123.2234,10.04.97,ST_MAN,8200,100,50                  
122,Payam,Kaufling,PKAUFLIN,650.123.3234,01.05.95,ST_MAN,7900,100,50            
123,Shanta,Vollman,SVOLLMAN,650.123.4234,10.10.97,ST_MAN,6500,100,50            
124,Kevin,Mourgos,KMOURGOS,650.123.5234,16.11.99,ST_MAN,5800,100,50             
125,Julia,Nayer,JNAYER,650.124.1214,16.07.97,ST_CLERK,3200,120,50               
126,Irene,Mikkilineni,IMIKKILI,650.124.1224,28.09.98,ST_CLERK,2700,120,50       
127,James,Landry,JLANDRY,650.124.1334,14.01.99,ST_CLERK,2400,120,50             
128,Steven,Markle,SMARKLE,650.124.1434,08.03.00,ST_CLERK,2200,120,50            
129,Laura,Bissot,LBISSOT,650.124.5234,20.08.97,ST_CLERK,3300,121,50             
130,Mozhe,Atkinson,MATKINSO,650.124.6234,30.10.97,ST_CLERK,2800,121,50          
131,James,Marlow,JAMRLOW,650.124.7234,16.02.97,ST_CLERK,2500,121,50             
132,TJ,Olson,TJOLSON,650.124.8234,10.04.99,ST_CLERK,2100,121,50                 
133,Jason,Mallin,JMALLIN,650.127.1934,14.06.96,ST_CLERK,3300,122,50             
134,Michael,Rogers,MROGERS,650.127.1834,26.08.98,ST_CLERK,2900,122,50           
135,Ki,Gee,KGEE,650.127.1734,12.12.99,ST_CLERK,2400,122,50                      
136,Hazel,Philtanker,HPHILTAN,650.127.1634,06.02.00,ST_CLERK,2200,122,50        
137,Renske,Ladwig,RLADWIG,650.121.1234,14.07.95,ST_CLERK,3600,123,50            
138,Stephen,Stiles,SSTILES,650.121.2034,26.10.97,ST_CLERK,3200,123,50           
139,John,Seo,JSEO,650.121.2019,12.02.98,ST_CLERK,2700,123,50                    
140,Joshua,Patel,JPATEL,650.121.1834,06.04.98,ST_CLERK,2500,123,50              
141,Trenna,Rajs,TRAJS,650.121.8009,17.10.95,ST_CLERK,3500,124,50                
142,Curtis,Davies,CDAVIES,650.121.2994,29.01.97,ST_CLERK,3100,124,50            
143,Randall,Matos,RMATOS,650.121.2874,15.03.98,ST_CLERK,2600,124,50             
144,Peter,Vargas,PVARGAS,650.121.2004,09.07.98,ST_CLERK,2500,124,50             
145,John,Russell,JRUSSEL,011.44.1344.429268,01.10.96,SA_MAN,14000,,4,100,80     
146,Karen,Partners,KPARTNER,011.44.1344.467268,05.01.97,SA_MAN,13500,,3,100,80  
147,Alberto,Errazuriz,AERRAZUR,011.44.1344.429278,10.03.97,SA_MAN,12000,,3,100,80                                                                    148,Gerald,Cambrault,GCAMBRAU,011.44.1344.619268,15.10.99,SA_MAN,11000,,3,100,80
149,Eleni,Zlotkey,EZLOTKEY,011.44.1344.429018,29.01.00,SA_MAN,10500,,2,100,80   
150,Peter,Tucker,PTUCKER,011.44.1344.129268,30.01.97,SA_REP,10000,,3,145,80     
151,David,Bernstein,DBERNSTE,011.44.1344.345268,24.03.97,SA_REP,9500,,25,145,80 
152,Peter,Hall,PHALL,011.44.1344.478968,20.08.97,SA_REP,9000,,25,145,80         
153,Christopher,Olsen,COLSEN,011.44.1344.498718,30.03.98,SA_REP,8000,,2,145,80  
154,Nanette,Cambrault,NCAMBRAU,011.44.1344.987668,09.12.98,SA_REP,7500,,2,145,80
155,Oliver,Tuvault,OTUVAULT,011.44.1344.486508,23.11.99,SA_REP,7000,,15,145,80  
156,Janette,King,JKING,011.44.1345.429268,30.01.96,SA_REP,10000,,35,146,80      
157,Patrick,Sully,PSULLY,011.44.1345.929268,04.03.96,SA_REP,9500,,35,146,80     
158,Allan,McEwen,AMCEWEN,011.44.1345.829268,01.08.96,SA_REP,9000,,35,146,80     
159,Lindsey,Smith,LSMITH,011.44.1345.729268,10.03.97,SA_REP,8000,,3,146,80      
160,Louise,Doran,LDORAN,011.44.1345.629268,15.12.97,SA_REP,7500,,3,146,80       
161,Sarath,Sewall,SSEWALL,011.44.1345.529268,03.11.98,SA_REP,7000,,25,146,80    
162,Clara,Vishney,CVISHNEY,011.44.1346.129268,11.11.97,SA_REP,10500,,25,147,80  
163,Danielle,Greene,DGREENE,011.44.1346.229268,19.03.99,SA_REP,9500,,15,147,80  
164,Mattea,Marvins,MMARVINS,011.44.1346.329268,24.01.00,SA_REP,7200,,1,147,80   
165,David,Lee,DLEE,011.44.1346.529268,23.02.00,SA_REP,6800,,1,147,80            
166,Sundar,Ande,SANDE,011.44.1346.629268,24.03.00,SA_REP,6400,,1,147,80         
167,Amit,Banda,ABANDA,011.44.1346.729268,21.04.00,SA_REP,6200,,1,147,80         
168,Lisa,Ozer,LOZER,011.44.1343.929268,11.03.97,SA_REP,11500,,25,148,80         
169,Harrison,Bloom,HBLOOM,011.44.1343.829268,23.03.98,SA_REP,10000,,2,148,80    
170,Tayler,Fox,TFOX,011.44.1343.729268,24.01.98,SA_REP,9600,,2,148,80           
171,William,Smith,WSMITH,011.44.1343.629268,23.02.99,SA_REP,7400,,15,148,80     
172,Elizabeth,Bates,EBATES,011.44.1343.529268,24.03.99,SA_REP,7300,,15,148,80   
173,Sundita,Kumar,SKUMAR,011.44.1343.329268,21.04.00,SA_REP,6100,,1,148,80      
174,Ellen,Abel,EABEL,011.44.1644.429267,11.05.96,SA_REP,11000,,3,149,80         
175,Alyssa,Hutton,AHUTTON,011.44.1644.429266,19.03.97,SA_REP,8800,,25,149,80    
176,Jonathon,Taylor,JTAYLOR,011.44.1644.429265,24.03.98,SA_REP,8600,,2,149,80   
177,Jack,Livingston,JLIVINGS,011.44.1644.429264,23.04.98,SA_REP,8400,,2,149,80  
178,Kimberely,Grant,KGRANT,011.44.1644.429263,24.05.99,SA_REP,7000,,15,149      
179,Charles,Johnson,CJOHNSON,011.44.1644.429262,04.01.00,SA_REP,6200,,1,149,80  
180,Winston,Taylor,WTAYLOR,650.507.9876,24.01.98,SH_CLERK,3200,120,50           
181,Jean,Fleaur,JFLEAUR,650.507.9877,23.02.98,SH_CLERK,3100,120,50              
182,Martha,Sullivan,MSULLIVA,650.507.9878,21.06.99,SH_CLERK,2500,120,50         
183,Girard,Geoni,GGEONI,650.507.9879,03.02.00,SH_CLERK,2800,120,50              
184,Nandita,Sarchand,NSARCHAN,650.509.1876,27.01.96,SH_CLERK,4200,121,50        
185,Alexis,Bull,ABULL,650.509.2876,20.02.97,SH_CLERK,4100,121,50                
186,Julia,Dellinger,JDELLING,650.509.3876,24.06.98,SH_CLERK,3400,121,50         
187,Anthony,Cabrio,ACABRIO,650.509.4876,07.02.99,SH_CLERK,3000,121,50           
188,Kelly,Chung,KCHUNG,650.505.1876,14.06.97,SH_CLERK,3800,122,50               
189,Jennifer,Dilly,JDILLY,650.505.2876,13.08.97,SH_CLERK,3600,122,50            
190,Timothy,Gates,TGATES,650.505.3876,11.07.98,SH_CLERK,2900,122,50             
191,Randall,Perkins,RPERKINS,650.505.4876,19.12.99,SH_CLERK,2500,122,50         
192,Sarah,Bell,SBELL,650.501.1876,04.02.96,SH_CLERK,4000,123,50                 
193,Britney,Everett,BEVERETT,650.501.2876,03.03.97,SH_CLERK,3900,123,50         
194,Samuel,McCain,SMCCAIN,650.501.3876,01.07.98,SH_CLERK,3200,123,50            
195,Vance,Jones,VJONES,650.501.4876,17.03.99,SH_CLERK,2800,123,50               
196,Alana,Walsh,AWALSH,650.507.9811,24.04.98,SH_CLERK,3100,124,50               
197,Kevin,Feeney,KFEENEY,650.507.9822,23.05.98,SH_CLERK,3000,124,50             
198,Donald,OConnell,DOCONNEL,650.507.9833,21.06.99,SH_CLERK,2600,124,50         
199,Douglas,Grant,DGRANT,650.507.9844,13.01.00,SH_CLERK,2600,124,50             
200,Jennifer,Whalen,JWHALEN,515.123.4444,17.09.87,AD_ASST,4400,101,10           
201,Michael,Hartstein,MHARTSTE,515.123.5555,17.02.96,MK_MAN,13000,100,20        
202,Pat,Fay,PFAY,603.123.6666,17.08.97,MK_REP,6000,201,20                       
203,Susan,Mavris,SMAVRIS,515.123.7777,07.06.94,HR_REP,6500,101,40               
204,Hermann,Baer,HBAER,515.123.8888,07.06.94,PR_REP,10000,101,70                
205,Shelley,Higgins,SHIGGINS,515.123.8080,07.06.94,AC_MGR,12000,101,110         
206,William,Gietz,WGIETZ,515.123.8181,07.06.94,AC_ACCOUNT,8300,205,110          

107 rows selected.
user10989620
michaels2 wrote:
no rows selected in SQL*Plus.
Do you have the DEPT table in your schema?

You can alos try with EMPLOYEES of course:
SQL> set pages 5000

SQL> select trim(column_value) data 
from xmltable('for $i in ROW return string-join($i/*,",")' passing dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
/
I am using different table and it has data.
I have just replaced the query inside, I am getting No rows returned message.
user10989620
ravikumar.sv wrote:

now i added ',' as delimiter to the query...
it is working for me..

you need to change at two places inside replace function and at rtrim function.

If the error still persists...please post the query as well as error which you are using...
Below is the query i am using,
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</' , </')),'/ROW/*/text()'),',') col
FROM TABLE
(SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from test_tab1').extract('ROWSET/ROW'))
FROM dual
);

I am getting the output but the , is not present in the output.

Edited by: user10989620 on Sep 9, 2009 2:30 AM
ravikumar.sv
Answer
SELECT rtrim(extract(xmltype(REPLACE(column_value,'</'',',</')),'/ROW/*/text()'),',') col
   FROM TABLE
  (SELECT xmlsequence(dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
     FROM dual
  );
check this..copy it from this directly

Ravi Kumar
Marked as Answer by user10989620 · Sep 27 2020
MichaelS
Just in case:
select trim(column_value) data 
  from xmltable('for $i in ./* return string-join($i/*,",")' passing dbms_xmlgen.getxmltype('select * from employees').extract('ROWSET/ROW'))
/ 
should also work ...
ravikumar.sv
Hi Michaels,

I tried your code but output is not coming from sql developer as well as from sql plus editor...
may be this is version problem or something else...
I am on 10g...

Ravi Kumar
1 - 23
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 7 2009
Added on Sep 9 2009
23 comments
27,888 views