Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Tree Puzzle ]

  1. We have a table called Hierarchies, here we have two columns named ParentID and ChildID. Each ChildID can appear again in this table as a Parent, with other children. Its like a tree structure.
  2. Please check out the sample input and expected output for details.

Sample Input

ParentID ChildID
1 2
2 3
3 4

Expected Output

ParentID ChildID
1 2
1 3
1 4
2 3
2 4
3 4

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


CREATE TABLE Hierarchies
(
	  ParentID INT
	, ChildID INT
)
GO

INSERT Hierarchies VALUES (1,2)
INSERT Hierarchies VALUES (2,3)
INSERT Hierarchies VALUES (3,4)




Update May 10 | Solutions – Pawan Kumar Khowal


--

;WITH CTE1 AS
(
	SELECT ParentID , ChildID FROM Hierarchies
	UNION ALL
	SELECT c.ParentID , H.ChildID FROM CTE1 c INNER JOIN Hierarchies H
	ON H.ParentID = c.ChildID 
)
SELECT * FROM CTE1 ORDER BY ParentID



--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com

Pawan Kumar Khowal

Advertisements