April 25, 2013 at 9:37 am
I have a table with structure Education(edu_id, emp_id, school, degree) where an employee can have multiple entries if they have multiple degrees.
I am trying to do a data extract with the following columns needed: emp_id, school1, degree1, school2, degree2, ..., school5, degree5.
So this looks like a pivot query. My idea is to create a CTE on the original table to add schooln and degreen on the original table based on the top 5 schools of an employee and then do a pivot. Any elegant idea on how to implement this?
Thanks,
Erick
April 26, 2013 at 7:08 am
There might be better solutions but I think this would do the trick.
With CTEEducation AS
(Select emp_id, school, degree,
ROW_NUMBER() over (partition by emp_id order by edu_id) as ListId,
ROW_NUMBER() over (partition by emp_id order by edu_id)+10 as ListId2
From Education)
Select emp_id, Max([1]) as [school1],Max([11]) as [degree1],
Max([2]) as [school2], Max([12]) as [degree2],
Max([3]) as [school3], Max([13]) as [degree3],
Max([4]) as [school4], Max([14]) as [degree4],
Max([5]) as [school5], Max([15]) as [degree5]
from (Select ListId,emp_id, school,ListId2, degree
From CTEEducation
where ListId <=5) P
pivot (max(school) for ListId in ([1], [2], [3], [4], [5])) AS Piv1
pivot (max(degree) for ListId2 in ([11], [12], [13], [14], [15])) AS Piv2
group by emp_id
order by 1
April 26, 2013 at 7:13 am
Thanks, Patrick. I cross-posted this question on the TSQL forum and geoff5 had a solution without pivot and only one CTE:
http://www.sqlservercentral.com/Forums/Topic1446659-392-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply