SQL Puzzle | Complete the Data Puzzle

In this puzzle you need to generate two rows for each combination of column a and column b for column c one with value 1 and another with value 2. After that for column D if any value of column c is there then retain that value other wise put zero in d column.For more details please check the sample input and expected output.

Sample Input

a b c d
11 1 2 12
11 9 1 5
11 10 2 12
11 11 1 2
11 11 2 15
11 12 1 13

Expected Output

a b c d
11 1 1 0
11 1 2 12
11 9 1 5
11 9 2 0
11 10 1 0
11 10 2 12
11 11 1 2
11 11 2 15
11 12 1 13
11 12 2 0

Script

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

 ```-- CREATE TABLE AllData ( a INT ,b INT ,c INT ,d INT ) GO INSERT INTO AllData VALUES (11,1 ,2,12), (11,9,1,5), (11,10,2,12), (11,11,1,2 ), (11,11,2,15 ), (11,12,1,13) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- select z.*,ISNULL(b.d,0) d from (SELECT DISTINCT c.a,c.b,a.t c FROM AllData c CROSS APPLY ( VALUES (1),(2))a(t) ) z LEFT JOIN AllData b ON b.a = z.a and b.b = z.b and b.c = z.c -- ```

Output-1

 ```-- /*------------------------ select z.*,ISNULL(b.d,0) d from (SELECT DISTINCT c.a,c.b,a.t c FROM AllData c CROSS APPLY ( VALUES (1),(2))a(t) ) z LEFT JOIN AllData b ON b.a = z.a and b.b = z.b and b.c = z.c ------------------------*/ a b c d ----------- ----------- ----------- ----------- 11 1 1 0 11 1 2 12 11 9 1 5 11 9 2 0 11 10 1 0 11 10 2 12 11 11 1 2 11 11 2 15 11 12 1 13 11 12 2 0 (11 row(s) affected) -- ```

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