Home » SQL & PL/SQL » SQL & PL/SQL » Questions on grouping.
Questions on grouping. [message #415330] Mon, 27 July 2009 05:05 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
I have a result of a select query below (1) :

(1)

PARENT_ID CHILD_ID ROLE_ID
---------- --------- ------------
A001 0001 BLUE
A001 0001 RED
A001 0001 GREEN
A001 0002 YELLOW
A001 0002 RED
A002 0001 BLUE
A003 0002 GREEN



The attributes PARENT_ID,CHILD_ID and ROLE_ID are all from different tables.

I want the results to be grouped by the PARENT_ID and CHILD_ID attributes and the ROLE_ID attribute to be displayed in one line delimited by comma like (2).

(2)

PARENT_ID CHILD_ID ROLE_ID
---------- --------- ------------
A001 0001 BLUE,RED,GREEN
A001 0002 YELLOW,RED
A002 0001 BLUE
A003 0002 GREEN


If this is possible please let me know.

Any help will be greatly appreciated.

Cheers
Steve

[Updated on: Mon, 27 July 2009 05:12]

Report message to a moderator

Re: Questions on grouping. [message #415339 is a reply to message #415330] Mon, 27 July 2009 05:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search the forum/google for STRAGG/pivot.

By
Vamsi
Re: Questions on grouping. [message #415348 is a reply to message #415330] Mon, 27 July 2009 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Any help will be greatly appreciated

Posting accordingly to guide is greatly appreciated
Posting a test case is greatly appreciated
Posting what you tried is greatly appreciated
Searching before posting is greatly appreciated

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Re: Questions on grouping. [message #415815 is a reply to message #415330] Wed, 29 July 2009 07:08 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

TRY THIS

SELECT distinct parent_id, child_id,
SUBSTR(comma_list, 2) roles
from (
SELECT parent_id, child_id, SYS_CONNECT_BY_PATH (ROLE, ',') comma_list,
rnum, row_count
FROM (SELECT parent_id, child_id, ROLE,
ROW_NUMBER () OVER (PARTITION BY parent_id, child_id ORDER BY parent_id,
child_id) rnum,
COUNT (*) OVER (PARTITION BY parent_id, child_id)
row_count
FROM (SELECT 'A001' parent_id, '0001' child_id, 'BLUE' ROLE
FROM DUAL
UNION
SELECT 'A002' parent_id, '0002' child_id, 'GREEN' ROLE
FROM DUAL
UNION
SELECT 'A003' parent_id, '0003' child_id, 'PINK' ROLE
FROM DUAL
UNION
SELECT 'A004' parent_id, '0004' child_id, 'RED' ROLE
FROM DUAL
UNION
SELECT 'A005' parent_id, '0005' child_id, 'PINK' ROLE
FROM DUAL
UNION
SELECT 'A001' parent_id, '0001' child_id, 'BLACK' ROLE
FROM DUAL
UNION
SELECT 'A001' parent_id, '0001' child_id, 'ORANGE' ROLE
FROM DUAL) a)
START WITH rnum = 1
CONNECT BY parent_id = PRIOR parent_id
and child_id = prior child_id
and rnum = PRIOR rnum + 1)
where rnum = row_count

Re: Questions on grouping. [message #415821 is a reply to message #415815] Wed, 29 July 2009 07:53 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: Indexes
Next Topic: columns to rows
Goto Forum:
  


Current Time: Mon Dec 05 15:09:22 CST 2016

Total time taken to generate the page: 0.10723 seconds