SQL Puzzle | The User Access Control Puzzle

Puzzle Statement

Well I must say this is a very very good puzzle. This puzzle was asked to me by my friend & colleague Deepak. He came to this scenario while working on a task. In this puzzle there are only 6 columns. Its basically a tree structure. column a is a parent. Column b is children of column a and so on. Now if column b has all value 0 then for column a the output will be distinct values from column a and 0 for column b. If you have non zero value in column b for a row then for column b that non zero column value will come and in column a 0 will come. For details check sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE DeepakPuzzle
(      
 a INT
,b INT
,c INT
,d INT
,e INT
,f INT
)
GO

INSERT INTO DeepakPuzzle VALUES
(5,5,1,0,0,0),
(5,5,2,3,0,0),
(5,4,3,4,0,0),
(5,4,3,5,1,0),
(5,4,4,6,2,0)
GO


GO
--

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

SOLUTION 1 | Using Cross APPLY

--

No solution 🙂

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com