Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

Question

I have something that looks like this (SQL Fiddle here):

Date        Location    Value
1/1/2012    Baltimore   36
1/1/2012    Houston     36
1/1/2012    Chicago     55
2/1/2012    Baltimore   49
2/1/2012    Houston     58
2/1/2012    Chicago     51
3/1/2012    Baltimore   11
3/1/2012    Houston     13
3/1/2012    Chicago     18

However, I want it to look like this:

Date        Baltimore   Houston     Chicago
1/1/2012    36          36          55
2/1/2012    49          58          51
3/1/2012    11          13          18

How do I group by the Date and create new columns for each distinct Location? I do not know the complete set of possible Locations. I'm working in Oracle 11g Release 2.

Answer 1

Another way to do this if the columns are known is using an aggregate and a CASE statement:

SELECT d,
  sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore,
  sum(case when loc = 'Houston' then v else 0 end) as Houston,
  sum(case when loc = 'Chicago' then v else 0 end) as Chicago
FROM test
group by d
order by d;

See SQL Fiddle with Demo

If you want to do this dynamically, then you can create a procedure to generate the SQL statement:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select d ';

    begin
        for x in (select distinct loc from test order by 1)
        loop
            sql_query := sql_query ||
                ' , sum(case when loc = '''||x.loc||''' then v end) as '||x.loc;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from test group by d order by 1';

        open p_cursor for sql_query;
    end;
/

Then to call it:

variable x refcursor
exec dynamic_pivot(:x)
print x

the result is:

D                                BALTIMORE    CHICAGO    HOUSTON
------------------------------- ---------- ---------- ----------
01-JAN-12 12.00.00.000000 AM            36         55         36
01-FEB-12 12.00.00.000000 AM            49         51         58
01-MAR-12 12.00.00.000000 AM            11         18         13

Answer 2

To use a normal PIVOT operation (in Oracle 11g R2), you need to know the locations:

with foo as
(
  select d,loc,v
  from test
)
select *
from foo
pivot (
sum(v)
  for loc in ('Baltimore','Houston','Chicago')
)
order by 1;

If you don't know all locations there are hacky ways of doing it, but they're not pretty. This Oracle forum thread has (hacky) examples that can be used when the columns aren't known.