Home » SQL & PL/SQL » SQL & PL/SQL » Column Output in a Single Line with , seperated
Column Output in a Single Line with , seperated [message #203050] Mon, 13 November 2006 13:05 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

SQL> select pym_id, trans_id from pymt where trans_id = 2629246;

   PYM_ID     TRANS_ID
---------- ----------
   2620548    2629246
   2620587    2629246
   

Each Trans_id will have more than 1 Pym_id.

In this case the desired output we need is
   PYM_ID			Trans_id
   
   "2620548,2620587" 		2629246


Basically PYM_ID should be comma seperated,and group by Trans_id.
How can i fit the column in 1 line with ' , ' seperated.


Thanks

[Updated on: Mon, 13 November 2006 13:13]

Report message to a moderator

Re: Column Output in a Single Line with , seperated [message #203063 is a reply to message #203050] Mon, 13 November 2006 14:47 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
What version of the database are you using, it makes a big difference in doing this.
Re: Column Output in a Single Line with , seperated [message #203064 is a reply to message #203063] Mon, 13 November 2006 14:50 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
hi,

It's Oracle 9i Rel 2.


Thanks for follow up
Re: Column Output in a Single Line with , seperated [message #203067 is a reply to message #203064] Mon, 13 November 2006 15:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
This might be the most frequently asked question in recent times.
Search the forum for sys_connect_by_path/stragg/concat_all
Re: Column Output in a Single Line with , seperated [message #203068 is a reply to message #203050] Mon, 13 November 2006 15:02 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
for a nice writeup on doing it, see the following link.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:2196162600402
Re: Column Output in a Single Line with , seperated [message #203072 is a reply to message #203068] Mon, 13 November 2006 15:40 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Bill,

That's really a excellent example and fits the requirement.


Thanks Again.
Re: Column Output in a Single Line with , seperated [message #203073 is a reply to message #203050] Mon, 13 November 2006 15:42 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Your welcome. I would suggest that (when you have time) you check out the ASKTOM.ORACLE.COM site. Tom Kyle is one of Oracle sharpest developers and his oracle supported site has a lot of very practical ideas. It is one of the first places I look when I have a question.
Re: Column Output in a Single Line with , seperated [message #203144 is a reply to message #203073] Tue, 14 November 2006 01:27 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Bill B wrote on Mon, 13 November 2006 22:42
Your welcome. I would suggest that (when you have time) you check out the ASKTOM.ORACLE.COM site. Tom Kyle is one of Oracle sharpest developers and his oracle supported site has a lot of very practical ideas. It is one of the first places I look when I have a question.
I can only agree: it is one of the links mentioned in the sticky. And for firefox users, there's a nice search plugin for the asktom website.

MHE
Previous Topic: Updation in a table
Next Topic: unique rows
Goto Forum:
  


Current Time: Thu Dec 08 18:32:17 CST 2016

Total time taken to generate the page: 0.09476 seconds