Recursion or not and how - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursion or not and how

Author

Topic

masteripper
Starting Member

25 Posts

Posted - 2012-04-10 : 07:39:02

Hello to eveybody...i am struggling with a situation and i would like some advice
I have this kind of structure in my table
ID.........ParentID.....Level
01...........NULL..........0
01-02........01............1
01-03........01............1
01-02-01...01-02.......2
01-02-02...01-02.......2
01-03-01...01-03.......2

What i need is "traverse" the tree in reverse order to produce the following result
Level0....Level1.......Level2
01.........01-02.....01-02-01
01.........01-03.....01-03-01
I want to give the ids of highest Level and produce all the backwards hierarcy
By the Way ...don't limit it to 3 levels (for now i have 6) unless is the only way
Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:07:18

have a look at common table expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masteripper
Starting Member

25 Posts

Posted - 2012-04-10 : 16:23:35

I have already CTE the table but i cannot "generate" the level columns...thats my problem
I know
ID.....ParentID...Level
xx.......Null......0
xx-xx.....xx.......1
xx-xx-xx..xx-xx...2

the problem is the showing of the hierarchy
level0....level1....level2............levelN
xx........xx-xx......xx-xx-xx......xx-xx-...xxx(n)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:30:08

you just need to PIVOT based on level values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masteripper
Starting Member

25 Posts

Posted - 2012-04-11 : 01:52:28

Well i have done ....i didn't want to change the compatibility Level of my DB but i checked it and it caused no problems so i moved on
Now the question is how to eliminate the NULLS
Now
01
....01-00
..........01-00-01
..........01-00-02
I want
01....01-00....01-00-01
01....01-00....01-00-02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 01:05:51

apply group by and max() over columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources