t-sql query - 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)

t-sql query

Author

Topic

rizwanmgm
Starting Member

8 Posts

Posted - 2012-05-01 : 06:48:02

dear friends,

i need your help.
i have one table and fields are
itemid name parentid
1 Root null
2 IT 1
3 sales 1
4 report 2
5 doc 4

now how can i query to find name doc and it should return me complete path like doc is under Root/it/report.
means on result i want to show the parentid as complete path.

i hope you understand.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-01 : 07:14:01

If you want to work frequently with hierarchies, you might want to see if the hierarchyid feature available in SQL 2008 would be of use to you. http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

If you just want to get the hierarchy tree as a one-time thing, you can use a recursive CTE like this:

;WITH cte AS
(
	SELECT	*,CAST('' AS VARCHAR(MAX)) AS [Path] 
	FROM	YourTable
	WHERE	parentId  IS NULL 
	
	UNION ALL
	
	SELECT	y.*, c.Path+'/'+c.NAME
	FROM	cte c
			INNER JOIN YourTable y ON y.parentId = c.ItemId
)
SELECT * FROM cte;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources