January 28, 2009 at 3:08 am
I have 1 table which contains self reference(parent-child relationship with the table).
below is a lookalike table sample
IF OBJECT_ID('tempdb..#tmp1') IS NOT NULL
DROP TABLE #tmp1
CREATE TABLE #tmp1(ColID INT, ParentColID INT, ColVal VARCHAR(20) )
INSERT#tmp1
SELECT1,NULL, 'Geography' UNION
SELECT2,1,'East' UNION
SELECT3,1,'West' UNION
SELECT4,1,'South' UNION
SELECT5,1,'North' UNION
SELECT6,2,'W' UNION
SELECT7,2,'A' UNION
SELECT8,3,'C' UNION
SELECT9,4,'A' UNION
SELECT10,4,'E' UNION
SELECT11, 4,'B'
i am trying to get the data as shown below... I used CTE but not working, help me out..
ColIDParentColIDColVal
1NULLGeography
21East
72A
62W
51North
41South
94A
114B
104E
31West
83C
the data should be sorted by ColVal and group by ColID, ParentCOlID no Sort col available in table.
Abhijit - http://abhijitmore.wordpress.com
January 28, 2009 at 5:23 am
WITH CTE(ColID,ParentColID,ColVal,fullpath) AS (
SELECT ColID,ParentColID,ColVal,CAST(ColVal AS VARCHAR(MAX))
FROM #tmp1
WHERE ParentColID IS NULL
UNION ALL
SELECT p.ColID , p.ParentColID ,p.ColVal, c.fullpath+'/'+CAST(p.ColVal AS VARCHAR(MAX))
FROM CTE c
INNER JOIN #tmp1 p ON p.ParentColID=c.ColID)
SELECT ColID,ParentColID,ColVal
FROM CTE
ORDER BY fullpath
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply