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.
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
Pivot / Crosstab Query in Oracle 10g (Dynamic column number)
Oracle 11g is the first to support PIVOT/UNPIVOT, so you have to use:
SELECT t.username,
MAX(CASE WHEN t.product = 'Chair' THEN t.numberpurchases ELSE NULL END) AS chair,
MAX(CASE WHEN t.product = 'Table' THEN t.numberpurchases ELSE NULL END) AS tbl,
MAX(CASE WHEN t.product = 'Bed' THEN t.numberpurchases ELSE NULL END) AS bed
FROM TABLE t
GROUP BY t.username
You could use DECODE, but CASE has been supported since 9i.
Pivot rows to columns based on content in Oracle 10g PL/SQL
To accomplish what you are looking for (and not be specific to this data) I believe you are going to need some extra fields in your tables. For example, you will need to know which questions are Single-Answer, Multi-Answer, and Text-Entry without having to look at the data. You will also need to know which answers are possible for your Multi-answer questions without having to link through the data. From there, you can loop through the meta information about each question / answer combination and build yourself a query to run that will return the data in your desired format. Something like:
/* Create Tables with Data - Note 2 new columns added to questions_text */
create table user_answers
as
select 123 user_id, 100 question_id, 1010 answer_id, null text_entry from dual
union all
select 123 user_id, 200 question_id, 2010 answer_id, null text_entry from dual
union all
select 123 user_id, 200 question_id, 2030 answer_id, null text_entry from dual
union all
select 123 user_id, 300 question_id, 3000 answer_id, 'code 789' text_entry from dual;
create table questions_text
as
select 100 question_id, 'Gender' question_text, 'S' question_type, 1000 answer_set_id from dual
union all
select 200 question_id, 'Interests' question_text, 'M' question_type, 2000 answer_set_id from dual
union all
select 300 question_id, 'Your code' question_text, 'T' question_type, 3000 answer_set_id from dual;
create table answers_text
as
select 1010 answer_id, 'Female' text, 1000 answer_set_id from dual
union all
select 1020 answer_id, 'Male' text, 1000 answer_set_id from dual
union all
select 2010 answer_id, 'Sports' text, 2000 answer_set_id from dual
union all
select 2020 answer_id, 'Computers' text, 2000 answer_set_id from dual
union all
select 2030 answer_id, 'Movies' text, 2000 answer_set_id from dual
union all
select 3000 answer_id, null text, 3000 answer_set_id from dual;
/* PL/SQL for creating SQL statement to return data in desired format */
declare
v_sql VARCHAR2(32767);
begin
v_sql := 'select ua.user_id "User",';
FOR question IN (
select question_id, question_text, question_type, answer_set_id
from questions_text
)
LOOP
IF question.question_type = 'M'
THEN
FOR answer IN (
select answer_id, text
from answers_text
where answer_set_id = question.answer_set_id
)
LOOP
v_sql := v_sql||chr(10)||'max(case when ua.question_id = '||question.question_id||' and ua.answer_id = '||answer.answer_id||' then 1 else 0 end) "'||answer.text||'",';
END LOOP;
ELSIF question.question_type = 'S'
THEN
v_sql := v_sql||chr(10)||'min(case when ua.question_id = '||question.question_id||' then at.text end) "'||question.question_text||'",';
ELSIF question.question_type = 'T'
THEN
v_sql := v_sql||chr(10)||'min(case when ua.question_id = '||question.question_id||' then ua.text_entry end) "'||question.question_text||'",';
END IF;
END LOOP;
v_sql := rtrim(v_sql,',');
v_sql := v_sql||' from
user_answers ua
inner join questions_text qt
on qt.question_id = ua.question_id
inner join answers_text at
on at.answer_id = ua.answer_id
group by
ua.user_id';
-- replace dbms_output with code to write file
dbms_output.put_line(v_sql);
END;
Related Topics
Can You Create Nested with Clauses for Common Table Expressions
How to Confirm a Database Is Oracle & What Version It Is Using SQL
How to List Active Connections on Postgresql
Database Schema for Organizing Historical Stock Data
How to Delete Multiple Rows in SQL Where Id = (X to Y)
Select Multiple Columns from a Table, But Group by One
Database/Sql: How to Store Longitude/Latitude Data
Sql, Postgres Oids, What Are They and Why Are They Useful
MySQL - How to Count All Rows Per Table in One Query
How to Merge the Columns from Two Tables into One Output
In General, Should Every Table in a Database Have an Identity Field to Use as a Pk
Create a One to Many Relationship Using SQL Server
How to Have Multiple "With As" in Single SQL - Oracle SQL
How to Get a Plain Text Postgres Database Dump on Heroku
Grant Privileges on Future Tables in Postgresql