How to pivot rows into columns (custom pivoting)

Question

I have a Sql Database table similar to the following:

Day   Period    Subject  Mon   1         Ch Mon   2         Ph Mon   3         Mth Mon   4         CS Mon   5         Lab1 Mon   6         Lab2 Mon   7         Lab3 Tue   1         Ph Tue   2         Ele Tue   3         Hu Tue   4         Ph Tue   5         En Tue   6         CS2 Tue   7         Mth 

I would like it displayed as follows: Kind of crosstab or Pivot

Day   P1   P2   P3   P4   P5   P6   P7  Mon   Ch   Ph   Mth  CS2  Lab1 Lab2 Lab3 Tue   Ph   Ele  Hu   Ph   En   CS2  Mth 

What would be the ideal way to do it? Can someone please show me the Sql code please?

Answer 1

You could probably do it with the PIVOT function, but I prefer the old school method:

SELECT     dy,     MAX(CASE WHEN period = 1 THEN subj ELSE NULL END) AS P1,     MAX(CASE WHEN period = 2 THEN subj ELSE NULL END) AS P2,     MAX(CASE WHEN period = 3 THEN subj ELSE NULL END) AS P3,     MAX(CASE WHEN period = 4 THEN subj ELSE NULL END) AS P4,     MAX(CASE WHEN period = 5 THEN subj ELSE NULL END) AS P5,     MAX(CASE WHEN period = 6 THEN subj ELSE NULL END) AS P6,     MAX(CASE WHEN period = 7 THEN subj ELSE NULL END) AS P7 FROM     Classes GROUP BY     dy ORDER BY     CASE dy         WHEN 'Mon' THEN 1         WHEN 'Tue' THEN 2         WHEN 'Wed' THEN 3         WHEN 'Thu' THEN 4         WHEN 'Fri' THEN 5         WHEN 'Sat' THEN 6         WHEN 'Sun' THEN 7         ELSE 8     END 

• I changed some column names to avoid reserved words

Answer 2

Just incase you do want the new school method. (The Pivot statement should work in SQL2005+, the VALUES bit for the example data only SQL2008)

WITH ExampleData AS ( SELECT X.*   FROM (VALUES   ('Mon', 1, 'Ch'), ('Mon', 2, 'Ph'), ('Mon', 3, 'Mth'), ('Mon', 4, 'CS'), ('Mon', 5, 'Lab1'), ('Mon', 6, 'Lab2'), ('Mon', 7, 'Lab3'), ('Tue', 1, 'Ph'), ('Tue', 2, 'Ele'), ('Tue', 3, 'Hu'), ('Tue', 4, 'Ph'), ('Tue', 5, 'En'), ('Tue', 6, 'CS2'), ('Tue', 7, 'Mth') ) AS X (Day,   Period,    Subject) )  SELECT Day, [1] AS P1, [2] AS P2,[3] AS P3, [4] AS P4, [5] AS P5,[6] AS P6,[7] AS P7 FROM ExampleData PIVOT   (   Max(Subject)   FOR Period IN ([1], [2],[3],[4], [5],[6], [7])   ) AS PivotTable;  

Result

Day  P1   P2   P3   P4   P5   P6   P7 ---- ---- ---- ---- ---- ---- ---- ---- Mon  Ch   Ph   Mth  CS   Lab1 Lab2 Lab3 Tue  Ph   Ele  Hu   Ph   En   CS2  Mth