OraFAQ Forum: SQL & PL/SQL » SQL Query two columns

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query two columns

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

SQL Query two columns [message #560804]

Tue, 17 July 2012 04:33

satheesh_ss
Messages: 54
Registered: July 2012

Member

Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.

Report message to a moderator

Re: SQL Query two columns [message #560806 is a reply to message #560804]

Tue, 17 July 2012 04:36

Michel Cadot
Messages: 54252
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Tue, 17 July 2012 11:35

From your previous topic:

Michel Cadot wrote on Sat, 14 July 2012 16:11
None.

From your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 19:52
...
You not only have to read the Concepts manual but also the Database SQL Reference.

Regards
Michel

Michel Cadot wrote on Thu, 12 July 2012 15:11
From your previous topic:

Michel Cadot wrote on Thu, 12 July 2012 14:08
Please feedback to your previous topics and thanks people that (try to) help you.
Also from your previous topics:

Michel Cadot wrote on Thu, 12 July 2012 08:39
All your questions are answered in Database Concepts.
Please read it.

Regards
Michel

Michel Cadot wrote on Thu, 12 July 2012 08:47
...
Database Concepts

Regards
Michel

Regards
Michel

In addition, this is a FAQ already posted a hundred time, so SEARCH before posting (accordingly to the guide).

Regards
Michel

What is your problem in writing this?
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste what you already tried.

Regards
Michel

Report message to a moderator

Re: SQL Query two columns [message #560828 is a reply to message #560806]

Tue, 17 July 2012 05:51

Littlefoot
Messages: 16998
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's one option:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL_N                                              NUMBER
 COL_C                                              VARCHAR2(20)

SQL> select * from test;

     COL_N COL_C
---------- --------------------
         1 a
         2 b
         3 c

SQL> select rtrim (xmlagg (xmlelement (e, (to_char(col_n) ||','|| col_c)|| ',')).extract ('//text()'), ',') result
  2  from test;

RESULT
--------------------------------------------------------------------------------
1,a,2,b,3,c

SQL>

Report message to a moderator