May 25, 2010 at 5:29 am
Hi, I have a parent-child relationship as under:
1 Node1 0
2 Child1_1 1
3 Child2_1 1
4 Node2 0
5 Child1_2 4
6 Child2_2 4
7 Child3_2 4
8 Node3 0
9 Child1_3 8.
Now I need to pull down the data from the database such that I can get the root node and the first three child columns in the output. In the above case the output should be like as under:
1 Node1 Child1_1 Child2_1 Null
2 Node2 Child1_2 Child2_2 Child3_2
3 Node3 Child1_3 Null Null
can anyone please help me in getting the proper SQL.
Regards,
Kunal
May 25, 2010 at 5:49 am
One doubt, will a parent have only 3 childs? If it is, then i have the code for you! Please clarify!
May 25, 2010 at 5:52 am
No...Its not necessary that the parent will have only 3 records. I may have 3, more than 3 or less than 3 child records.
But still and go ahead from ur side will be appreciated.
Thanks!!!
May 25, 2010 at 6:21 am
As you are first time poster, you probably wont be aware of how to post the data in ready-to-use format, so i took the onus on my side and created the DDLs..
Sample Table and Sample Data:
IF OBJECT_ID('TEMPDB..#Relations') IS NOT NULL
DROP TABLE #Relations
CREATE TABLE #Relations
(
ID INT,
Member VARCHAR(100),
Parent_ID INT
)
INSERT INTO #Relations (ID,Member,Parent_ID )
SELECT 1 ,'Node1', 0
UNION ALL SELECT 2 , 'Child1_1', 1
UNION ALL SELECT 3 ,'Child2_1', 1
UNION ALL SELECT 4 ,'Node2', 0
UNION ALL SELECT 5 ,'Child1_2', 4
UNION ALL SELECT 6 ,'Child2_2', 4
UNION ALL SELECT 7 ,'Child3_2', 4
UNION ALL SELECT 8 ,'Node3', 0
UNION ALL SELECT 9 ,'Child1_3', 8
SELECT * FROM #Relations
Now the code that will transpose the sample data:
Assumption here is , A Parent wil have only 3 childs :
Now the code:
;WITH HierarchyCTE (ID ,Member ,Parent_ID )
AS
(
SELECT EH.ID, EH.Member , EH.Parent_ID
FROM #Relations EH
WHERE EH.Parent_ID = 0
UNION ALL
SELECT EH.ID, EH.Member , EH.Parent_ID
FROM #Relations EH
INNER JOIN HierarchyCTE CTE ON EH.Parent_ID = CTE.ID
),
FINAL_CTE AS
(
SELECT CTE.ID,
CTE.Member,
Emp.Member Parent,
ROW_NUMBER() OVER(PARTITION BY Emp.Member ORDER BY CTE.ID) RN
FROM HierarchyCTE CTE
LEFT JOIN #Relations Emp ON Emp.ID = CTE.Parent_ID
WHERE Emp.Member IS NOT NULL
)
SELECT * FROM
(SELECT Member , Parent , RN FROM FINAL_CTE) PIVOT_TABLE
PIVOT
(MAX(Member) FOR RN IN ([1],[2],[3])) PIVOT_HANDLE
Hope this gets you started; For future posts please go through the following article on how to post data in a readily-consumable format so that many of the volunteers will help you with tested code.
Link for the article: CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
C'est Pras!
May 25, 2010 at 6:54 am
Thanks a lot!!
It had really helped me by large in coming out of my problem!!!
May 25, 2010 at 7:03 am
You're welcome , desai!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply