filter out id and nested id's - 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)

filter out id and nested id's

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-14 : 02:02:16

Hi

I have a table that look like this..


NodeID (pk)  ParentNodeID (int) text (nvarchar)

And have sample data like this..


1 null null products
2 null null administration
3 2 add products
4 2 edit products
5 null news

As you can see the parentnodeid is related to nodeid, I would like a query that filter out all nodeid and nested parentnodeid based on Nodeid value/s

Can someone please show how this can be done?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-14 : 02:12:59

check out recursive CTE

refer to BOL http://msdn.microsoft.com/en-us/library/ms186243.aspx
or just search the internet for more examples

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-14 : 23:29:29

quote:

Originally posted by magmo


1 null null products
2 null null administration
3 2 add products
4 2 edit products
5 null news

Your sample data show me a Forest Structure. you have three root nodes.

______________________
Mayazar Moori Ke Dane Kesh Ast
(http://www.nimaheydarian.com/gallery/main.php?g2_view=core.DownloadItem&g2_itemId=55&g2_serialNumber=1)

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-15 : 03:00:27

No, there are only one root node (NodeID), but there is one obsolete column, sorry. The correct structure is..

NodeID ParentNodeID Text

1 null "products"
2 null "administration"
3 2 "add products"
4 2 "edit products"
5 null "news"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-15 : 03:18:07

[code]
;With CTE (NodeID,ParentNodeID,Text,Path)
AS
(
SELECT NodeID,ParentNodeID,Text,CAST(NodeID AS varchar(8000))
FROM Table
WHERE ParentNodeID IS NULL
UNION ALL
SELECT t.NodeID, t.ParentNodeID, t.Text,c.Path + '/' + CAST(t.NodeID AS varchar(20))
FROM CTE c
JOIN Table t
ON t.ParentNodeID=c.NodeID
)
SELECT *
FROM CTE
OPTION (MAXRECURSION 0)
[/code]

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2010-05-17 : 02:18:36

Hi visakh16

Thank you very much for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:03:47

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources