T-SQL Query | [ The Tree Puzzle ]

Tags

Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], The Biggest Gap Puzzle, The Gap Puzzle, The Tree Puzzle, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle

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