Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rashmi Dupati
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2596
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Rashmi Dupati
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic