Forum Stats

  • 3,853,207 Users
  • 2,264,192 Discussions
  • 7,905,286 Comments

Discussions

pivot table on date

655776
655776 Member Posts: 12
edited Nov 19, 2008 4:47PM in SQL & PL/SQL
I have a table called SCHEDULE with the following fields:
date,
red,
orange,
yellow,
green

where each color represents an event for the given date. I want to do some kind of pivot table that will make each column correspond to a date, and have a single row for each color. I've read the other threads about pivot tables, but I don't know how to apply them to my situation.

See below for an example of what I want to do:

original table SCHEDULE:
date red orange yellow green
============================
19-Nov-08 John Bob John Tom
20-Nov-08 Bob Tom Tom Tom


result of pivot:
19-Nov-08 20-Nov-08
=========================
John Bob
Bob Tom
John Tom
Tom Tom
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Nov 19, 2008 3:51PM Answer ✓
    Hi,

    First you have to "un-pivot" your data, so it looks like
    color  dt          name
    ------ ----------- ----------
    red    19-Nov-2008 John
    orange 19-Nov-2008 Bob
    ...
    green  20-Nov-2008 Tom
    You can do this by cross-joining the schedule table with a table that has one row for each color.
    If you don't have such a table, I recommend you make one, but, if you don't want to, you can generate one on the fly:
    WITH  colors  AS
    (
        SELECT 'red'    AS color_name FROM DUAL  UNION ALL
        SELECT 'orange' AS color_name FROM DUAL  UNION ALL
       ...
    )
    SELECT  color_name
    ,       dt
    ,       CASE
                WHEN color_name = 'red'    THEN red
                WHEN color_name = 'orange' THEN orange
                ...
            END  AS name
    FROM        schedule
    CROSS JOIN  colors
    Then pivot back, GROUPing BY color_name:
    SELECT    color_name
    ,         MAX (CASE WHEN TRUNC (dt) = TO_DATE ('19-Nov-2008', 'DD-Mon-YYYY') THEN name END)
                     AS nov_19
    ,         MAX (CASE WHEN TRUNC (dt) = TO_DATE ('20-Nov-2008', 'DD-Mon-YYYY') THEN name END)
                     AS nov_20
    FROM      query_given_above
    GROUP BY  color_name;

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,492 Red Diamond
    edited Nov 19, 2008 3:51PM Answer ✓
    Hi,

    First you have to "un-pivot" your data, so it looks like
    color  dt          name
    ------ ----------- ----------
    red    19-Nov-2008 John
    orange 19-Nov-2008 Bob
    ...
    green  20-Nov-2008 Tom
    You can do this by cross-joining the schedule table with a table that has one row for each color.
    If you don't have such a table, I recommend you make one, but, if you don't want to, you can generate one on the fly:
    WITH  colors  AS
    (
        SELECT 'red'    AS color_name FROM DUAL  UNION ALL
        SELECT 'orange' AS color_name FROM DUAL  UNION ALL
       ...
    )
    SELECT  color_name
    ,       dt
    ,       CASE
                WHEN color_name = 'red'    THEN red
                WHEN color_name = 'orange' THEN orange
                ...
            END  AS name
    FROM        schedule
    CROSS JOIN  colors
    Then pivot back, GROUPing BY color_name:
    SELECT    color_name
    ,         MAX (CASE WHEN TRUNC (dt) = TO_DATE ('19-Nov-2008', 'DD-Mon-YYYY') THEN name END)
                     AS nov_19
    ,         MAX (CASE WHEN TRUNC (dt) = TO_DATE ('20-Nov-2008', 'DD-Mon-YYYY') THEN name END)
                     AS nov_20
    FROM      query_given_above
    GROUP BY  color_name;
  • 655776
    655776 Member Posts: 12
    Thanks Frank! That answered my question completely!
This discussion has been closed.