Query help - 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 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-13 : 16:21:28

I need a recursive query which provides the ouptput of hierarchy



Here is sample data.

Table:studentA

ID    Iname    Parent   
---   ----     ------
1     tx        Null 
2     tx1        1
3     tx2        1
4     vt        Null
5     VT1        4  
6     KT        Null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:32:16

[code]
With Hierarchy_CTE
AS
(
SELECT ID,Iname
FROM studentA
WHERE Parent IS NULL
UNION ALL
SELECT s.ID,s.lname
FROM Hierarchy_CTE c
INNER JOIN studentA s
ON s.Parent = c.ID
)
SELECT *
FROM Hierarchy_CTE

OPTION (MAXRECURSION 0)
[/code]

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-14 : 11:25:11

thanks visakh..

what would be the output from the query because i got all listed values in the table..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 11:29:04

see this to understand the hierarchy


With Hierarchy_CTE
AS
(
SELECT ID,Iname,CAST(lname AS varchar(8000)) AS Path
FROM studentA
WHERE Parent  IS NULL
UNION ALL
SELECT s.ID,s.lname,CAST(c.Path + '/' + s.lname AS varchar(8000))
FROM Hierarchy_CTE c
INNER JOIN studentA s
ON s.Parent = c.ID
)
SELECT *
FROM Hierarchy_CTE

OPTION (MAXRECURSION 0)

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-14 : 11:59:39

Thanks Visakh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 12:33:02

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources