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?

score 21 · Accepted Answer · answered Jun 25 '10 at 19:41

21

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

answered Jun 25 '10 at 19:41

Tom H

46,766
14
87
128

+1: You were faster & have the ORDER BY. So I'll just add: As you can see, dynamic columns will require using dynamic SQL. There is ANSI PIVOT syntax, but it's only supported on SQL Server 2005+ and Oracle 11g. – OMG Ponies Jun 25 '10 at 19:42
I'm presently doing this in SQLite, but it appears to be a fairly intensive operation. Does anyone know if there is a more performant solution? – EnemyBagJones Jan 30 '18 at 20:27
I don't believe that SQLite has any kind of built in pivot function, so this is probably what you're stuck with. I don't use SQLite though, so maybe someone with more experience with that specific SQL vendor has a better idea. – Tom H Jan 30 '18 at 21:21

score 13 · Answer 2 · answered Jun 25 '10 at 19:50

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

Why Max(Subject)? Is this because PIVOT has to take an aggregate function? — JBRWilkinson, Jul 06 '10 at 11:41
@JBRWilkinson Yes. Exactly. There should only be one matching record for a Day/Period combination so 'MIN' would have worked equally well. — Martin Smith, Jul 06 '10 at 12:02

score 2 · Answer 3 · answered Jun 25 '10 at 19:40

You could try...

SELECT DISTINCT Day,
       (SELECT Subject
            FROM my_table mt2
            WHERE mt2.Day = mt.Day AND
                  Period  = 1) AS P1,
       (SELECT Subject
            FROM my_table mt2
            WHERE mt2.Day = mt.Day AND
                  Period  = 2) AS P2,
   .
   .
   etc
   .
   .
   .
   (SELECT Subject
        FROM my_table mt2
        WHERE mt2.Day = mt.Day AND
              Period  = 7) AS P7
FROM my_table mt;

but I can't say I like it very much. Better than nothing, though.

score 1 · Answer 4 · edited Jan 20 '15 at 08:19

DECLARE @TIMETABLE TABLE (
    [Day]       CHAR(3),
    [Period]    TINYINT,
    [Subject]   CHAR(5)
)
INSERT INTO @TIMETABLE([Day], [Period], [Subject])
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')

SELECT 
    [Day],
    MAX(CASE [Period] WHEN 1 THEN [Subject] END) AS P1,
    MAX(CASE [Period] WHEN 2 THEN [Subject] END) AS P2,
    MAX(CASE [Period] WHEN 3 THEN [Subject] END) AS P3,
    MAX(CASE [Period] WHEN 4 THEN [Subject] END) AS P4,
    MAX(CASE [Period] WHEN 5 THEN [Subject] END) AS P5,
    MAX(CASE [Period] WHEN 6 THEN [Subject] END) AS P6,
    MAX(CASE [Period] WHEN 7 THEN [Subject] END) AS P7
FROM @TIMETABLE
GROUP BY [Day]

score 1 · Answer 5 · answered Jun 25 '10 at 19:48

Use cross apply to get all the values in a comma delimted format in a single column. instead of "7" different columns. The following query can be used for any column-> row mapping

SELECT DISTINCT Day, [DerivedColumn] FROM <Table> A CROSS APPLY ( SELECT Period + ',' FROM <Table> B WHERE A.Day = B.Day Order By Period FOR XML PATH('') ) AS C (DerivedColumn)

You will get [Ch,Ph,Mth,CS2,Lab1,Lab2,Lab3] in one column for Mon and so on ... You could use this as a table to query for any particular Day.

Hope this helps

score 0 · Answer 6 · answered Dec 20 '13 at 07:07

with pivot_data as
(
select [day], -- groping column
period, -- spreading column
subject -- aggreate column
from pivot_tb
)
select [day],  [1] AS P1, [2] AS P2,[3] AS P3, [4] AS P4, [5] AS P5,[6] AS P6,[7] AS P7
from pivot_data
pivot ( max(subject) for period in ([1], [2],[3],[4], [5],[6], [7]) ) as p;

How to pivot rows into columns (custom pivoting)

6 Answers6

Linked

Related