Pivoting data makes a long, narrow result shorter and wider. For instance you can run a query that lists all majors for each tech_id, so there will be several rows for some tech_ids. But, what if we want one record per tech_id and majors listed out in separate columns.
Pivots don't require the use of row_number(), it was just useful in this example.
select tech_id, [1] as Major1, [2] as Major2, [3] as Major3
from (
select tech_id, major_id,
row_number() over (partition by tech_id order by major_rank, seq_nbr) row
from st_major
where end_date > getdate()
) a
pivot ( max(major_id) for row in ( [1], [2], [3] ) ) as pivottable
A couple notes:
There may be more than three majors for a student. Pivot tables make us enumerate all the rows we're going to pivot. So, you need to decide what the maximum number you could run into will be, and expand the SQL to inculde [4], [5], etc.
Under the covers, SQL Server is doing a GROUP BY when you use this technique. It needs to know what to group by, and it groups by all the columns in the inner query not listed in the PIVOT statement. In the command above it is grouping by TECH_ID. So, if the inner query had returned any extra columns (like END_DATE for instance) it would have broken the results.
If you want to pivot more then one variable, or have an older version of SQL Server (prior to 2008), then you'll need to use a GROUP BY technique instead of PIVOT. The following returns one record per student along with the grade recieved and term in which the course was taken for several courses. We are pivitong on both grade and term. When a course is taken multiple times, we're returning the earliest term/grade:
select TECH_ID
,max(case when Course='CMST191' then GRADE end) CMST191_Grade
,max(case when Course='CMST191' then YRTR end) CMST191_YRTR
,max(case when Course='ENG111' then GRADE end) ENG111_Grade
,max(case when Course='ENG111' then YRTR end) ENG111_YRTR
,max(case when Course='BIOL211' then GRADE end) BIOL211_Grade
,max(case when Course='BIOL211' then YRTR end) BIOL211_YRTR
from (
select s.TECH_ID, s.YRTR
-- Speech 191 and 192 are really the same course
,replace(c.subj+c.COU_NBR, 'CMST192', 'CMST191') Course
,s.GRADE
,row_number() over (partition by s.TECH_ID, replace(c.subj+c.COU_NBR, 'CMST192', 'CMST191') order by s.YRTR) row
from ST_COU s
join CT_COU c
on s.COU_ID=c.COU_ID and s.YRTR=c.YRTR
where drop_time_stamp is null
and c.subj+c.COU_NBR in ('ENG111', 'CMST191', 'CMST192', 'BIOL211')
) c
where row=1
group by TECH_ID
Both of the techniques above require you to specify exactly what values exist of the columns you are pivoting. Many times when we are pivoting, we are creating a concatinated string of the pivitoted values to display to the user. For instance, we may be writing a report that returns one record per student, and in one cell we want to show all their majors. In this situation, there is a XML trick that is fast and doesn't require you know set a maximum number of majors:
select distinct tech_id
,replace(stuff((
select '###' + major_id [text()]
from st_major
where end_date > getdate()
and tech_id=m.tech_id
order by major_rank, seq_nbr
for xml path('')
), 1, 3, ''), '###', char(13)+char(10)) Majors
from st_major m
where end_date > getdate()
If the column being pivoted might contain XML entities like ampersands or apostrophes, then use the XML .value() method to cast the returned string from XML to character data:
select c.CourseId,
stuff((
select ', ' + p.Email [text()]
from dbo.StudentEnrollment as e
join dbo.Person as p
on p.PersonId = e.StudentId
where c.CourseId = e.CourseId
and e.DropDate is null
order by p.Email
for xml path(''), type).value('.', 'varchar(8000)')
, 1, 2, '') StudentEmailList
from dbo.Course c
where c.TermId between 20153 and 20173
and c.NumberEnrolled > 0
group by c.CourseId
order by c.CourseId