pivot table on date

655776

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

Frank Kulash

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;

Frank Kulash

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

Thanks Frank! That answered my question completely!