SQL Puzzle | Multi Column Pivot Puzzle ( Gmail )

Tags

SQL Puzzle | Multi Column Pivot Puzzle ( Gmail )

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

2 thoughts on “SQL Puzzle | Multi Column Pivot Puzzle ( Gmail )”

Pingback: SQL Puzzle | Dynamic Pivot Puzzle | Improving my SQL BI Skills
revank219 said:

May 17, 2021 at 7:50 PM

;with cte as (
select EMPID,EMPNAME,manid,manname,
case when Manid =2 then ‘Manid1’
when manid = 5 then ‘Manid2’
when manid = 7 then ‘Manid3’
when manid = 8 then ‘Manid4’
when manid = 4 then ‘Manid5’ end as multiple_mngrs,
case when Manname = ‘Myna’ then ‘Manname1’
when Manname = ‘Shiva’ then ‘Manname2’
when Manname = ‘Rama’ then ‘Manname3’
when Manname = ‘Krishna’ then ‘Manname4’
when Manname = ‘Venkat’ then ‘Manname5’ end as multiple_mngrs_names
from TestPiv
),cte1 as (
select empid,empname,Manid1,Manname1,Manid2,Manname2,manid3,Manname3,manid4,Manname4,manid5,Manname5 from cte
pivot (max(manid) for multiple_mngrs in (Manid1,Manid2,manid3,manid4,manid5) )a
pivot (max(manname) for multiple_mngrs_names in (Manname1,Manname2,Manname3,Manname4,Manname5) )b
)
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(manid5)manid5,max(Manname5)Manname5
from cte1
group by empid,empname

LikeLiked by 1 person

M	T	W	T	F	S	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

Share this

Related

2 thoughts on “SQL Puzzle | Multi Column Pivot Puzzle ( Gmail )”

Leave a comment Cancel reply