SQL Puzzle | The Yes No Pivot Puzzle

In this puzzle you have to check for each Id whether a, b, c and d values are present or not. If yes then you have output ‘Yes’ else ‘No’ for each a, b, c and d column. For more details please check the sample input and expected output.

Sample Input

Id Vals
1 a
1 b
1 c
1 d
2 a
2 d
3 d

Expected Output

Id a b c d
1 Yes Yes Yes Yes
2 Yes No No Yes
3 No No No Yes

Script

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

 ```-- CREATE TABLE Table1 ( Id VARCHAR(10) ,Vals VARCHAR(10) ) GO INSERT INTO Table1 VALUES (1, 'a'), (1, 'b'), (1, 'c'), (1, 'd'), (2, 'a'), (2, 'd'), (3, 'd') GO SELECT * FROM Table1 GO -- ```

Rules/Restrictions

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

SOLUTION 1 | USING PIVOT

 ```-- ;WITH CTE AS ( SELECT * , 'Yes' Ya FROM Table1 ) SELECT Id, ISNULL([a],'No') [a],ISNULL([b],'No') [b],ISNULL([c],'No') [c],ISNULL([d],'No') [d] FROM CTE PIVOT ( MAX(Ya) FOR Vals IN ([a],[b],[c],[d]) )t -- ```

Output

 ```-- Id a b c d ---------- ---- ---- ---- ---- 1 Yes Yes Yes Yes 2 Yes No No Yes 3 No No No Yes (3 rows affected) -- ```

SOLUTION 2 | USING GROUP BY and MAX

 ```-- SELECT Id ,MAX(CASE WHEN Vals = 'a' THEN 'Yes' ELSE 'No' END) [a] ,MAX(CASE WHEN Vals = 'b' THEN 'Yes' ELSE 'No' END) [b] ,MAX(CASE WHEN Vals = 'c' THEN 'Yes' ELSE 'No' END) [c] ,MAX(CASE WHEN Vals = 'd' THEN 'Yes' ELSE 'No' END) [d] FROM Table1 GROUP BY Id -- ```

Output

 ```-- Id a b c d ---------- ---- ---- ---- ---- 1 Yes Yes Yes Yes 2 Yes No No Yes 3 No No No Yes (3 rows 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