Home » SQL & PL/SQL » SQL & PL/SQL » tree base query help
tree base query help [message #264704] Tue, 04 September 2007 04:40 Go to next message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
hi all

i have slight query regarding tree structure query

I have the data like this

------emp_test-------
empno name mgr

101 a
102 b 101
103 c 102
104 d 101
105 e 102
106 f 104
107 g 105
108 h 104
109 i 101

the hierarchy will be

level NAMEHIRARCHY

1 a
2 b
3 c
3 e
4 g
2 d
3 f
3 h
2 i


but i need the data like this in a single select query
I have created a function .but i need in select query

level NAMEHIRARCHY comma

1 a a
2 b a,b
3 c a,b,c
3 e a,b,e
4 g a,b,e,g
2 d a,d
3 f a,d,f
3 h a,d,h
2 i a,i


Please help me to solve this query

Thanks in advance

Nusrat
Re: tree base query help [message #264720 is a reply to message #264704] Tue, 04 September 2007 05:34 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you checked out SYS_CONNECT_BY_PATH already? If not search the board for it. And while you're at it. There's a user-defined aggregate function you could use as well. It's called 'CONCAT_ALL' and the source is posted several times on the board.

So, hit "search"!

MHE
Re: tree base query help [message #264734 is a reply to message #264704] Tue, 04 September 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, you have to always post your Oracle version (4 decimals) as solution almost always depends on it.
If you post a test case (create table and insert statements) you'll likely have several solutions as more people will be able to search and find them.
For myself, reading your post I know I can do it but without a test case I will not search.

Regards
Michel
Re: tree base query help [message #264746 is a reply to message #264734] Tue, 04 September 2007 07:36 Go to previous message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
Hi

SYS_CONNECT_BY_PATH is working very well

Thanks Maaher and Michel

Regards

Nusrat
Previous Topic: Trying (and failing) to use PL/SQL variable in a XML query in a cursor
Next Topic: Using WHENEVER ERROR in login.sql
Goto Forum:
  


Current Time: Sat Dec 10 03:01:02 CST 2016

Total time taken to generate the page: 0.07525 seconds