Pivot to three named columns


Students may have more than one race.I want to add races to report with new headings: Race1, Race2 and Race3 only.

s.lastfirst, s.grade_level,
max(case when sr.racecd = 100 then 100 else 0 end)race100,
max(case when sr.racecd = 201 then 201 else 0 end)race201,
max(case when sr.racecd = 300 then 300 else 0 end)race300,
max(case when sr.racecd = 400 then 400 else 0 end)race400,
max(case when sr.racecd = 600 then 600 else 0 end)race600,
max(case when sr.racecd = 700 then 700 else 0 end)race700
from students s, studentrace sr
where s.enroll_status=0
and s.id=sr.studentid
group by s.lastfirst,s.grade_level
order by s.grade_level ASC

Best Answer

This needs a variation, here pivoting can be done using a ROW_NUMBER:

select lastfirst, grade_level,
   max(case when rn = 1 then racecd end) race1, -- find 1st row
   max(case when rn = 2 then racecd end) race2, -- find 2nd row
   max(case when rn = 3 then racecd end) race3  -- find 3rd row
      s.lastfirst, s.grade_level, sr.racecd,
      row_number() -- one row per race, numbered 1,2,3,...
      over (partition by studentid 
            order by raced) rn
   from students s
   join studentrace sr
     on s.id=sr.studentid
   where s.enroll_status=0
 ) dt
group by last first, grade_level
order by grade_level ASC