Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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
share|improve this answer

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.

share|improve this answer
    
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. –  Jake Feasel 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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