Tags

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


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

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

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