Tags

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


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements