Complicated Pivot
I have reduced a complex schema to the following samples
Students
- StudentID int, Name varchar(50)
- 1, Bill
- 2, Amy
- 3, Beth
- 4, Scott
- 5, Steve
Classes
- ClassID int, Name varchar(50), Period varchar(50)
- 1, Algebra, Period1
- 2, Geography, Period3
- 3, Biology, Period5
- 4, Physics, Period4
- 5, Speech, Period2
- 开发者_开发百科6, History, Period6
and a junction table
StudentsClasses
- StudentID int, ClassID int
- 1, 1
- 1, 4
- 1, 5
- 2, 6
- 3, 5
- 3, 4
- 3, 6
- 4, 1
- 4, 4
- 5, 5
- 5, 6
My goal is to list each student with their selected classes listed in period order. I have the following select
SELECT Name,Period1, Period2, Period3,
Period4, Period5, Period6
FROM (
SELECT _Students.Name AS [NAME],_Classes.Period AS PIVOT_CODE, _Classes.name as [Class]
FROM _Classes
INNER JOIN _StudentsClasses ON _Classes.ClassID=_StudentsClasses.ClassID
INNER JOIN _Students ON _StudentsClasses.StudentID=_Students.StudentID
)
AS data
PIVOT
( min([Class]) FOR [PIVOT_CODE] IN
(Period1, Period2, Period3,
Period4, Period5, Period6)
) AS pvt
Which results in
Name Period1 Period2 Period3 Period4 Period5 Period6
------ --------- --------- --------- --------- --------- ----------
Amy NULL NULL NULL NULL NULL History
Beth NULL Speech NULL Physics NULL History
Bill Algebra Speech NULL Physics NULL NULL
Scott Algebra NULL NULL Physics NULL NULL
Steve NULL Speech NULL NULL NULL History
Where I need help is I need is to move all non-nulls towards the left column so there are no blanks. The column names can be renamed, for example
Name Choice1 Choice2 Choice3 Choice4 Choice5 Choice6
------ --------- --------- --------- --------- --------- ----------
Amy History
Beth Speech Physics History
Bill Algebra Speech Physics
Scott Algebra Physics
Steve Speech History
I can do this by selecting the pivot into a temp table then iterating over each row/column with a cursor but I'd like to avoid that. Any suggestions are greatly appreciated.
Assuming SQL Server 2005 (at least), using ROW_NUMBER()
to order the choices:
SELECT Name, Choice1, Choice2, Choice3, Choice4, Choice5, Choice6
FROM (
SELECT
S.Name AS [NAME],
'Choice' + CAST(ROW_NUMBER() OVER(PARTITION BY S.Name ORDER BY S.Name, C.Period) AS VARCHAR) AS PIVOT_CODE,
C.Name as [Class]
FROM Classes C
JOIN StudentsClasses SC ON C.ClassID = SC.ClassID
JOIN Students S ON SC.StudentID = S.StudentID
)
AS data
PIVOT
( min([Class]) FOR [PIVOT_CODE] IN
(Choice1, Choice2, Choice3, Choice4, Choice5, Choice6)
) AS pvt
精彩评论