Oracle10G SQL Pivot

Pivot on Oracle 10g

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

PIVOT function in Oracle 10g

Oracle 10 doesn't have pivot, so you can use conditional aggregation:

select sku,
       max(case when code = 'BRAND' then value end) as brand,
       max(case when code = 'CSBC' then value end) as CSBC,
       max(case when code = 'DWPS' then value end) as DWPS,
       max(case when code = 'DWS' then value end) as DWS,
       max(case when code = 'DWSG' then value end) as DWSG,
       max(case when code = 'EA' then value end) as ea
from t
group by sku;

Unable to PIVOT on ORACLE 10G DATABASE

You could use conditional aggregation:

SELECT ANALYST
    ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
    ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
    ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
    ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
    ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
    ,COUNT(*) AS Grand_Tot
FROM tab
GROUP BY ANALYST
ORDER BY ANALYST;

---

EDIT:

Can i know how to SUM the Grand_Tot Column along with that query i was trying SUM(Grand_Tot) but getting error as invalid identifier.

One way is to use subquery:

SELECT sub.*, SUM(Grand_Tot) OVER() AS Sum_Grant_Tot
FROM (
    SELECT ANALYST
        ,COUNT(CASE WHEN status = 'unchecked'   THEN 1 END) AS unchecked
        ,COUNT(CASE WHEN status = 'observation' THEN 1 END) AS observation
        ,COUNT(CASE WHEN status = 'supervisor'  THEN 1 END) AS supervisor
        ,COUNT(CASE WHEN status = 'pending'     THEN 1 END) AS pending
        ,COUNT(CASE WHEN status = 'closed'      THEN 1 END) AS closed
        ,COUNT(*) AS Grand_Tot
    FROM tab
    GROUP BY ANALYST
) sub
ORDER BY ANALYST;

Pivoting rows to columns in Oracle 10g

I must mention here that Oracle does not allow dynamic pivots. You can read about it here.

What I can only suggest is that you can use static pivots through queries though.

I have modified your data just a little bit - I have used the T_REF column as serially numbered. Here is my solution:

WITH tbl AS (
SELECT 1 T_REF
       , 'RUR' T_CUR_CODE
       , 181 T_TERM
       , 365 T_TERM_MAX
       , 5000 T_REST1
       , '10,8' T_RATE1
       , '50000,01' T_REST2
       , '11,1' T_RATE2
       , '500000,01' T_REST3
       , '11,2' T_RATE3
FROM DUAL
UNION
SELECT 2, 'RUR', 366, 730, 5000, '11,4', '50000,01', '11,55', '500000,01', '11,6' FROM dual
UNION
SELECT 3, 'EUR', 181, 365, 100, '5,7', '1500,01', '5,9', '20000,01', '6' FROM dual
UNION
SELECT 4, 'EUR', 366, 730, 100, '6,05', '1500,01', '6,2', '20000,01', '6,3' FROM dual
UNION
SELECT 5, 'USD', 181, 365, 100, '5,9', '1500,01', '6,1', '20000,01', '6,55' FROM dual
UNION
SELECT 6, 'USD', 366, 730, 100, '6,3', '1500,01', '6,4', '20000,01', '6,6' FROM dual
)
SELECT t_cur_code
       , COUNT(*) AS cnt
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_RATE1 ELSE NULL END) AS t_rate_1
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_1
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_1
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_REST1 ELSE NULL END) AS T_REST_1
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_RATE2 ELSE NULL END) AS T_RATE_2
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_2
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_2
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_REST2 ELSE NULL END) AS T_REST_2
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_RATE3 ELSE NULL END) AS T_RATE_3
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_3
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_3
       , MAX(CASE MOD(t_ref, 2) WHEN 1 THEN T_REST3 ELSE NULL END) AS T_REST_3
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_RATE1 ELSE NULL END) AS t_rate_4
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_4
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_4
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_REST1 ELSE NULL END) AS T_REST_4
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_RATE2 ELSE NULL END) AS T_RATE_5
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_5
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_5
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_REST2 ELSE NULL END) AS T_REST_5
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_RATE3 ELSE NULL END) AS T_RATE_6
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM ELSE NULL END) AS T_TERM_6
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_TERM_MAX ELSE NULL END) AS T_TERM_MAX_6
       , MAX(CASE MOD(t_ref, 0) WHEN 1 THEN T_REST3 ELSE NULL END) AS T_REST_6
FROM tbl
GROUP BY t_cur_code
;

This may look lengthy but it is incredibly simple. I have used COUNT(*) in the select list so you can see how many rows or records you have for that T_CUR_CODE. If the number is not 2, it's time to change the query for you.

I have used MAX function that would select only one value in multiple rows, the rest of the values are ought to be null.

I hope this helps.

How to do a pivot on the oracle database with field type varchar2

Oracle 11 has specialized PIVOT functionality (a nice introduction is here), but 10g doesn't. Here's how to do it in 10g:

SELECT
  student_id,
  MAX(CASE WHEN student_key = 'name' THEN student_value END) AS StudentName,
  MAX(CASE WHEN student_key = 'age' THEN student_value END) AS Age,
  MAX(CASE WHEN student_key = 'result' THEN student_value END) AS Result
FROM myTable
GROUP BY student_id

As with the Oracle PIVOT command, you need to know the number of output columns ahead of time.

Also note that MAX is used because we're rolling rows up to columns, which means we have to group, which means we need an aggregate function, so why not MAX? If you use MIN instead of MAX you'll get the same results.

Pivot in Oracle 10g and 11g r2

Q1. The where clause may be included prior to the pivot:

select * from
(
  SELECT  ename, edate
  FROM ppl

  WHERE .......
)
PIVOT
(
  COUNT(edate)
  FOR edate in('01-oct-2017', '02-oct-2017')
)
order by ename;

Q2. Yes you can have many case expressions, it will not be a big performance hit.

Or is there any feasible way to do this better? You could implement it through "dynamic SQL" instead, but the query structure will remain the same. Dynamic sql just builds the query SQL for you.

Oracle10g SQL pivot

I prefer using the GROUP BY solution with CASE expression.

SELECT 
    id,
    MAX(CASE WHEN emailRank = 1 THEN email END) AS [1],
    MAX(CASE WHEN emailRank = 2 THEN email END) AS [2],
    MAX(CASE WHEN emailRank = 3 THEN email END) AS [3],
    MAX(CASE WHEN emailRank = 4 THEN email END) AS [4]
FROM (
    SELECT
        id, 
        email, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
    FROM TABLE
)
GROUP BY id;

Original Pivot example had type and missing ")". Try the following to get pivot working:

pivot( max(email) FOR emailRank IN (1,2,3));

PIVOT in ORACLE 10G

solved using following procedure:
CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select PLAN_DATE ';
begin
for x in (select distinct ITEM_CODE,qty from XXES_DAILY_PLAN_TRAN where PLAN_DATE='27-04-2016')
loop
sql_query := sql_query ||
' , sum(case when qty = '''||x.qty||''' then qty else 0 end) as '||x.ITEM_CODE;
dbms_output.put_line(sql_query);
end loop;
sql_query := sql_query || ' from XXES_DAILY_PLAN_TRAN where PLAN_DATE=''27-04-2016'' group by PLAN_DATE ';
dbms_output.put_line(sql_query);
open p_cursor for sql_query;
end;

//calling

variable x refcursor
exec dynamic_pivot(:x)
print x

---

Related Topics