Home » SQL & PL/SQL » SQL & PL/SQL » Tree Structure - Concat String on Group By
Tree Structure - Concat String on Group By [message #284404] Thu, 29 November 2007 09:45 Go to next message
mthubiere
Messages: 5
Registered: November 2007
Junior Member
Hello,

Ive got a tree structure (to manage folders and documents 300000 objects) stored on a table as below:

DATAID PARENTID NAME
1 -1 MARVEL
2 1 CUSTOMERS
3 2 AIRLINES
4 3 PRESS
5 4 Presentation1.ppt
6 4 Presentation2.ppt
7 3 RESULTS
8 7 Result1.ppt

I would like to obtain this result:

DATAID PATH
5 MARVEL:CUSTOMERS:AIRLINES:PRESS
6 MARVEL:CUSTOMERS:AIRLINES:PRESS
8 MARVEL:CUSTOMERS:AIRLINES:RESULTS


I tried with a concat function and a cursor as I seen on the forum, but I got the limit of nb cursor openned.

So, I tried with sys_connect_by_path and ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), but I cannot retrieve the correct order. I tried to use LEVEL in order by but without success???

So, could you help me.


Re: Tree Structure - Concat String on Group By [message #284411 is a reply to message #284404] Thu, 29 November 2007 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Post a test case: create table and insert statements.

Regards
Michel
Re: Tree Structure - Concat String on Group By [message #284433 is a reply to message #284404] Thu, 29 November 2007 11:57 Go to previous messageGo to next message
mthubiere
Messages: 5
Registered: November 2007
Junior Member
Sorry for the format.

Oracle 9.1.2.8

/************** Table Creation ********************/

CREATE TABLE DTREE ( 
  DATAID       NUMBER (10), 
  PARENTID     NUMBER (10), 
  SUBTYPE      NUMBER (10),
  NAME        VARCHAR2 (255))

INSERT INTO A269_MARVEL_DTREE VALUES (1,-1,0,'MARVEL');
INSERT INTO A269_MARVEL_DTREE VALUES (2,1,0,'CUSTOMERS');
INSERT INTO A269_MARVEL_DTREE VALUES (3,2,0,'AIRLINES');
INSERT INTO A269_MARVEL_DTREE VALUES (4,3,0,'PRESS');
INSERT INTO A269_MARVEL_DTREE VALUES (5,4,144,'Presentation1.ppt');
INSERT INTO A269_MARVEL_DTREE VALUES (6,4,144,'Presentation2.ppt');
INSERT INTO A269_MARVEL_DTREE VALUES (7,3,0,'RESULTS');
INSERT INTO A269_MARVEL_DTREE VALUES (8,7,144,'Result1.ppt');


/**************************************************/



Re: Tree Structure - Concat String on Group By [message #284436 is a reply to message #284433] Thu, 29 November 2007 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you already try?
Post it, this will:
1/ maybe find the error
2/ prevent us from trying the same thing if it does not work
3/ give us a starting point

Regards
Michel

[Updated on: Thu, 29 November 2007 12:16]

Report message to a moderator

Re: Tree Structure - Concat String on Group By [message #284574 is a reply to message #284404] Fri, 30 November 2007 02:44 Go to previous messageGo to next message
mthubiere
Messages: 5
Registered: November 2007
Junior Member
I can retrieve the list of parents in hierarchical order but i cannot concat parents name.

select dataid, parentid, name from a269_marvel_dtree
start with dataid = 8 connect by prior parentid = dataid order by rowid asc

1	MARVEL
2	CUSTOMERS
3	AIRLINES
7	RESULTS
8	Result1.ppt



I would like to obtain for each document the path as below:

8       MARVEL:CUSTOMERS:AIRLINES:RESULTS

Re: Tree Structure - Concat String on Group By [message #284580 is a reply to message #284574] Fri, 30 November 2007 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use sys_connect_by_path.
You said in your first post you tried it.
Post what you tried, you should be able to get your result with this function.

Regards
Michel
Re: Tree Structure - Concat String on Group By [message #284582 is a reply to message #284404] Fri, 30 November 2007 03:47 Go to previous messageGo to next message
mthubiere
Messages: 5
Registered: November 2007
Junior Member
Yes indeed, but I cannot retrive correct result...

select dataid, sys_connect_by_path(name,':') from a269_marvel_dtree
start with dataid = 8 connect by prior parentid = dataid


8	:Result1.ppt
7	:Result1.ppt:RESULTS
3	:Result1.ppt:RESULTS:AIRLINES
2	:Result1.ppt:RESULTS:AIRLINES:CUSTOMERS
1	:Result1.ppt:RESULTS:AIRLINES:CUSTOMERS:MARVEL



I found some interesting solutions as
http://www.williamrobertson.net/documents/one-row.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

But none on hierarchical order...
Re: Tree Structure - Concat String on Group By [message #284587 is a reply to message #284582] Fri, 30 November 2007 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at CONNECT_BY_ISLEAF
You then can get only the leave.

If the order of the result is not the proper one then either
- go through the tree in reverse order
- reverse the result string (using a custom function)

Regards
Michel
Re: Tree Structure - Concat String on Group By [message #284678 is a reply to message #284404] Fri, 30 November 2007 09:21 Go to previous message
mthubiere
Messages: 5
Registered: November 2007
Junior Member
I found a solution with this request.

select dataid, substr(sys_connect_by_path(name,':'),2) from dtree
where dataid in (select distinct dataid from dtree where subtype = 144) 
start with dataid = 1 connect by prior dataid = parentid


Thank you very much for your help.
Previous Topic: searching across two tables
Next Topic: Find the date between (merged)
Goto Forum:
  


Current Time: Thu Dec 08 02:22:02 CST 2016

Total time taken to generate the page: 0.13525 seconds