Tags

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


SQL Puzzle | Multi Column Pivot Puzzle ( Gmail )

Gmail - Multi Column Pivot Puzzle

Gmail – Multi Column Pivot Puzzle

This puzzle was asked to me via gmail. Here the ask was to pivot data based on multiple columns. Please check out the sample input values and sample expected output below.

Sample Input

EmpID EmpName ManId ManName
1 Alan 2 Myna
1 Alan 5 Shiva
1 Alan 7 Rama
1 Alan 8 Krishna
1 Alan 4 Venkat
2 Manju 2 Myna
2 Manju 5 Shiva
2 Manju 7 Rama
2 Manju 8 Krishna

Expected Output

EmpID EmpName ManId1 ManName1 ManId2 ManName2 ManId3 ManName3 ManId4 ManName4 ManId5 ManName5
1 Alan 2 Myna 5 Shiva 7 Rama 8 Krishna 4 Venkat
2 Manju 2 Myna 5 Shiva 7 Rama 8 Krishna NULL NULL

Script

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

--

Create Table TestPiv
( 
  EmpID	Numeric(5),
  EmpName Varchar(20),
  ManId		Numeric(5),
  ManName	Varchar(20)
)

Insert Into TestPiv values (1,'Alan',2,'Myna')  
Insert Into TestPiv values (1,'Alan',5,'Shiva')  
Insert Into TestPiv values (1,'Alan',7,'Rama')  
Insert Into TestPiv values (1,'Alan',8,'Krishna')  
Insert Into TestPiv values (1,'Alan',4,'Venkat')  
Insert Into TestPiv values (2,'Manju',2,'Myna')  
Insert Into TestPiv values (2,'Manju',5,'Shiva')  
Insert Into TestPiv values (2,'Manju',7,'Rama')  
Insert Into TestPiv values (2,'Manju',8,'Krishna')  

--

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 Multi Column Pivot


--

SELECT EmpID, EmpName
,MAX([ManId1]) ManId1,MAX([ManName1]) ManName1
,MAX([ManId2]) ManId2,MAX([ManName2]) ManName2 
,MAX([ManId3]) ManId3,MAX([ManName3]) ManName3 
,MAX([ManId4]) ManId4,MAX([ManName4]) ManName4 
,MAX([ManId5]) ManId5,MAX([ManName5]) ManName5
FROM 
(
SELECT  *
FROM
(
SELECT *,CONCAT('ManId',rnk) c1 , CONCAT('ManName',rnk) c2 FROM
(
SELECT *, 
ROW_NUMBER() OVER (PARTITION BY EmpId ORDER BY (SELECT NULL)) rnk FROM TestPiv
)e		
)k
PIVOT ( MAX(ManId) FOR c1 IN ([ManId1],[ManId2],[ManId3],[ManId4],[ManId5]) )y1
PIVOT ( MAX(ManName) FOR c2 IN ([ManName1],[ManName2],[ManName3],[ManName4],[ManName5]) )y2
)t
GROUP BY EmpID,EmpName	
	
--

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