File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Displaying a Single Record with a Comma Separated Column from Multiple Records Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Displaying a Single Record with a Comma Separated Column from Multiple Records" Watch "Displaying a Single Record with a Comma Separated Column from Multiple Records" New topic
Author

Displaying a Single Record with a Comma Separated Column from Multiple Records

Rashmi Dupati
Greenhorn

Joined: Mar 05, 2007
Posts: 22
Hi All,

Please give a sample query for Displaying a Single Record with a Comma Separated Column from Multiple Records using Oracle.

the output should be like this:

id items
----- ------------------------------------------
1 Skiing, Diving
2 Diving, Skiing, Hunting, Fishing
4 Sailing, Skiing
5 Skiing

thanks in advance

Regards
Rashmi
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

I don't think a query can do that. (but you can challenge me).
Can't you create the separated string in your java code?


OCUP UML fundamental and ITIL foundation
youtube channel
Rashmi Dupati
Greenhorn

Joined: Mar 05, 2007
Posts: 22
Hi There,

please see the below code(it is MYSQL) but i need the query in oracle.


In this table for each “id_no” there is one or more “items” identified. Each record contains a single “item” value. I will use the code listed below to populate the above table:
- Hide quoted text -
-- create example table
CREATE TABLE Example2(id_no int not null, item varchar(20) not null)
-- populate the example table
INSERT INTO Example2 VALUES (1, 'Skiing')
INSERT INTO Example2 VALUES (1, 'Diving')
INSERT INTO Example2 VALUES (2, 'Diving')
INSERT INTO Example2 VALUES (2, 'Skiing')
INSERT INTO Example2 VALUES (2, 'Hunting')
INSERT INTO Example2 VALUES (2, 'Fishing')
INSERT INTO Example2 VALUES (4, 'Sailing')
INSERT INTO Example2 VALUES (4, 'Skiing')
INSERT INTO Example2 VALUES (5, 'Skiing')
The next code snippet returns a record set that contains a single record for each “id_no”, followed by a comma delimited string that concatenates each “item” value together into a single column value:
-- declare local variables
declare @p varchar(1000)
declare @i char(5)
declare @sm int
declare @m int
-- Print Report Heading
print 'id_no' + ' items'
print '----- ' + '------------------------------------------'
set @p = ''
-- set @m to the first id number
select top 1 @m = id_no from Example2
order by id_no
set @sm = 0
-- Process each id_no until no more items
while @m <> @sm
begin
set @sm = @m
-- string together all items with a comma between
select @i = id_no, @p = case when @p = '' then item else @p + ', ' + item end
from Example2 a
where id_no = @m
-- print id_no, and comma delimited string
print @i + ' ' + @p
-- increment id number
select top 1 @m = id_no from Example2
where id_no > @sm
order by id_no
set @p = ''
end
-- remove example table
drop table Example2
When I run this code against my Example2 table I get the following output:
id_no items
----- ------------------------------------------
1 Skiing, Diving
2 Diving, Skiing, Hunting, Fishing
4 Sailing, Skiing
5 Skiing
Let me explain how this code works. This code iteratively process each “id_no” value using a WHILE loop. Each pass through the WHILE loop strings together all the “item” values for a given “id_no”. The variable @m contains the value of the “id_no” for the records being collapsed into a single record. The following SELECT statement does all the work to collapse all the records for a given “id_no” value into a single row in the output:
select @i = id_no, @p = case when @p = '' then item else @p + ', ' + item end
from Example2 a
where id_no = @m


Declare @Description varchar(4000)
select @Description = coalesce(@Description + ',' , '') + Description
FROM ud_LookupMGR_Data where dataid in (81,82,83,84)

Thanks & Regards
Rashmi
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Displaying a Single Record with a Comma Separated Column from Multiple Records