Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.   
                You'll need to give a more clear example of what your result should be with the sample table.
– Joachim Isaksson
                Nov 16, 2012 at 6:31
                there is not static sql solution unless your T1.. Tn amount is finite and known. otherwise, dynamic sql and ref cursors are your options
– Kirill Leontev
                Nov 16, 2012 at 11:41
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)
    sql_query varchar2(1000) := 'select usr ';
    begin
        for x in (select distinct tp from temp order by 1)
            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;
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,
  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
                ,I need a column between USER and T1 to show total count of Types,how can it be done through query.Output should be like:-      | USR |Total | T1 | T2 | T3|                                       ----------------------      Total column  will show sum of T1,T2,T3
– F11
                Nov 30, 2012 at 4:23
                @little your best bet is it place your query inside of another select, similar to this demo - sqlfiddle.com/#!4/a42bb/5
– Taryn
                Nov 30, 2012 at 10:11
                ,I need one more help.I have to show total of all rows with header.Total will be added with header where total is sum of that column.Like User|Total_Total|T1_Total|T2_Total|
– F11
                Dec 26, 2012 at 6:24
                Then you will use column aliases on the select statement. So select usr as User, etc. but if you have another question you should post a new question.
– Taryn
                Dec 26, 2012 at 6:30
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
  FETCH V_CUR INTO V_ROW;
  EXIT WHEN V_CUR%NOTFOUND;
    V_COLUMNHEADING:=CONCAT(V_COLUMNHEADING,''''||V_ROW."TYPE"||''' AS "'||V_ROW."TYPE"||'",');
  END LOOP;
  IF NVL(V_COLUMNHEADING,' ') <> ' ' THEN
        V_COLUMNHEADING := SUBSTR(V_COLUMNHEADING,0,LENGTH(V_COLUMNHEADING)-1);
  END IF;
  V_QUERY := CONCAT(V_QUERY,'select * from (select * from temp_tbl)a pivot (sum("COUNT")for "TYPE" in ('||V_COLUMNHEADING||'))b;');
  DBMS_OUTPUT.PUT_LINE(V_QUERY);
END ;
To select data, use statement OPEN CUR_TURN_TIME FOR V_QUERY; in place of DBMS_OUTPUT.PUT_LINE(V_QUERY);, where CUR_TURN_TIME is the cursor declared in the package.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.