F11 F11 - 1 year ago 189
SQL Question

Pivot on Oracle 10g

I am using oracle 10g.
I have a temp table TEMP.

TEMP has following structure:-

USER COUNT TYPE
---- ----- ----
1 10 T1
2 21 T2
3 45 T1
1 7 T1
2 1 T3


I need a query which will show all types has column names,and types can have any value like
T1, T2,..Tn
and result will be like:-

USER T1 T2 T3
---- -- -- --
1 17 0 1
2 0 21 1
3 45 0 0


and User column will show all the users and
T1, T2
column will show total count of types.

Answer Source

In Oracle 10g, there was no PIVOT function but you can replicate it using an aggregate with a CASE:

select usr,
  sum(case when tp ='T1' then cnt else 0 end) T1,
  sum(case when tp ='T2' then cnt else 0 end) T2,
  sum(case when tp ='T3' then cnt else 0 end) T3
from temp
group by usr;

See SQL Fiddle with Demo

If you have Oracle 11g+ then you can use the PIVOT function:

select *
from temp
pivot
(
  sum(cnt)
  for tp in ('T1', 'T2', 'T3')
) piv

See SQL Fiddle with Demo

If you have an unknown number of values to transform, then you can create a procedure to generate a dynamic version of this:

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

    begin
        for x in (select distinct tp from temp order by 1)
        loop
            sql_query := sql_query ||
              ' , sum(case when tp = '''||x.tp||''' then cnt else 0 end) as '||x.tp;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from temp group by usr';

        open p_cursor for sql_query;
    end;
/

then to execute the code:

variable x refcursor
exec dynamic_pivot(:x)
print x

The result for all versions is the same:

| USR | T1 | T2 | T3 |
----------------------
|   1 | 17 |  0 |  0 |
|   2 |  0 | 21 |  1 |
|   3 | 45 |  0 |  0 |

Edit: Based on your comment if you want a Total field, the easiest way is to place the query inside of another SELECT similar to this:

select usr,
  T1 + T2 + T3 as Total,
  T1,
  T2,
  T3
from
(
  select usr,
    sum(case when tp ='T1' then cnt else 0 end) T1,
    sum(case when tp ='T2' then cnt else 0 end) T2,
    sum(case when tp ='T3' then cnt else 0 end) T3
  from temp
  group by usr
) src;

See SQL Fiddle with Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download