April 25, 2013 at 11:52 am
I posted this on the general thread but this looks like the better forum:
I have a table with structure Education(edu_id, emp_id, school, year, 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
My solution so far involves 4 CTEs: 1st uses RANK OVER PARTITION to append the pivot column to the table and 3 CTEs for pivots on school, year and degree plus a SELECT that joins all 4 CTEs. Any ideas for a more elegant solution?
April 25, 2013 at 12:38 pm
You really only need one CTE to rank the data, and then in the main query use a combination of aggregate functions and CASE statements to pivot the results into columns.
with
Ranked_Data as
(select emp_id, school, year, degree, position = rank() over (partition by emp_id order by year) from Education)
select
emp_id,
school1 = max(case when position = 1 then school end),
year1 = max(case when position = 1 then year end),
degree1 = max(case when position = 1 then degree end),
school2 = max(case when position = 2 then school end),
year2 = max(case when position = 2 then year end),
degree2 = max(case when position = 2 then degree end),
school3 = max(case when position = 3 then school end),
year3 = max(case when position = 3 then year end),
degree3 = max(case when position = 3 then degree end),
school4 = max(case when position = 4 then school end),
year4 = max(case when position = 4 then year end),
degree4 = max(case when position = 4 then degree end),
school5 = max(case when position = 5 then school end),
year5 = max(case when position = 5 then year end),
degree5 = max(case when position = 5 then degree end)
from
Ranked_Data
group by
emp_id
April 25, 2013 at 2:03 pm
Thanks, geoff5! Your solution's much shorter and easier to understand.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply