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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy