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.


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

  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)
    sql_query varchar2(1000) := 'select d ';

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

        end loop;

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

        open p_cursor for sql_query;

Then to call it:

variable x refcursor
exec dynamic_pivot(:x)
print x

the result is:

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

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 (
  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.

  • BTW, I edited your sqlfiddle.
    – Philᵀᴹ
    Oct 2 '12 at 17:08
  • Phil - I don't understand your SQL Fiddle comment. If you edited his Fiddle, then you should have gotten a new link associated with your edit. It doesn't look like you posted that link anywhere, so there is no way for anyone to see your edits. Oct 3 '12 at 6:51
  • @JakeFeasel Ah, ok. I didn't know how it worked in that regard
    – Philᵀᴹ
    Oct 3 '12 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.