Pivot on Oracle 10g

Question

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  0
   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 1

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

Answer 2

Here is a code for table creation:

CREATE TABLE TBL_TEMP
(
    USR   NUMBER
   ,CNT   NUMBER
   ,TP    VARCHAR2 (10)
);

INSERT INTO TBL_TEMP VALUES (1,10,'T1');
INSERT INTO TBL_TEMP  VALUES (2,21,'T2');
INSERT INTO TBL_TEMP  VALUES (3,45,'T1');
INSERT INTO TBL_TEMP  VALUES (1,7,'T1');
INSERT INTO TBL_TEMP  VALUES (2,1,'T3');

And, here is a code for your request:

  SELECT T1.USR
        ,SUM (T1) T1
        ,SUM (T2) T2
        ,SUM (T3) T3
    FROM (SELECT DISTINCT USR FROM TBL_TEMP) T1
        ,(SELECT T2.USR
                ,DECODE (T2.TP, 'T1', T2.CNT, 0) T1
                ,DECODE (T2.TP, 'T2', T2.CNT, 0) T2
                ,DECODE (T2.TP, 'T3', T2.CNT, 0) T3
            FROM TBL_TEMP T2) T2
   WHERE T1.USR = T2.USR
GROUP BY T1.USR;

And, the result is what you wanted.

Check it at SQL Fiddle Link here