Oracle pivot operator
You are getting the output like that simply because you are issuing select statement against a table (your tbl table) which presumably contains a column(primary key column for instance) which uniquely identifies a row and pivot operator takes into consideration values of that column. Here is a simple example:
/*assume it's your table tbl */
with tbl(unique_col, col1, col2) as(
select 1, 'a', 'a1' from dual union all
select 2, 'b', 'b1' from dual union all
select 3, 'c', 'c1' from dual
)
A query against such a table will give you that output(undesirable output) you provided in the question:
select A,B
from tbl
pivot(
max(col2) for col1 in ('a' as A,'b' as B)
)
Result:
A B
-- --
a1 null
null b1
In order to produce desired output, you need to exclude the column with unique value for a row:
select A
, B
from (select col1
, col2 /*selecting only those columns we are interested in*/
from tbl )
pivot(
max(col2) for col1 in ('a' as A,'b' as B)
)
Result:
A B
-- --
a1 b1
ORACLE one hot encoding: Use PIVOT operator for all available columns
You can use PL/SQL anonymous block to build the query automatically no matter how many distinct products you have in your table, but you need to understand that you can't put more than 1000 values into a pivot clause, as you can't have more than 1000 columns.
I would do something like this ( assuming always less 1000 values )
Test case ( create the table and the test values )
SQL> create table t ( CLID number , PRODUCT varchar2(10) ) ;
Table created.
SQL> insert into t ( clid , product )
2 with x ( a , b ) as
(
select 1 , 'A' from dual union all
select 1 , 'B' from dual union all
select 2 , 'A' from dual union all
select 2 , 'C' from dual union all
select 3 , 'A' from dual union all
select 3 , 'C' from dual union all
select 4 , 'D' from dual union all
select 4 , 'E' from dual union all
select 5 , 'B' from dual union all
select 5 , 'C' from dual union all
select 5 , 'D' from dual union all
select 5 , 'E' from dual
)
select a , b from x ;
12 rows created.
SQL> commit ;
Commit complete.
PLSQL construction
Then, to get automatically the query no matter how many different products
set serveroutput on size unlimited lines 220 pages 0
declare
v_query clob;
out_string varchar2(100);
cursor c_ids
is
select distinct product, count(distinct(product)) over () tot_rows from t order by 1 asc;
procedure print_clob_to_output (p_clob in clob)
is
l_offset pls_integer := 1;
l_chars pls_integer;
begin
loop
exit when l_offset > dbms_lob.getlength(p_clob);
l_chars := dbms_lob.instr(p_clob, chr(10), l_offset, 1);
if l_chars is null or l_chars = 0 then
l_chars := dbms_lob.getlength(p_clob) + 1;
end if;
dbms_output.put_line(dbms_lob.substr(p_clob, l_chars - l_offset, l_offset));
l_offset := l_chars + 1;
end loop;
end print_clob_to_output;
begin
dbms_output.enable(null);
for item in c_ids
loop
if item.tot_rows >= 1000
then
raise_application_error(-20001,'Maximum number of 1000 columns are not allowed',true);
end if;
out_string := item.product;
if c_ids%rowcount = 1
then
v_query := 'select * from (';
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' select * ');
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' from t ');
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' pivot( ');
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' count(product) ');
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' for product in ( '''||out_string||''' , ');
elsif c_ids%rowcount < item.tot_rows then
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' '''||out_string||''' ,');
else
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' '''||out_string||''' ) ');
end if;
end loop;
dbms_lob.append(v_query,''||chr(10)||'');
dbms_lob.append(v_query,' ) )');
print_clob_to_output(v_query);
end;
/
Execution
SQL> @query.sql
SQL> set serveroutput on size unlimited lines 220 pages 0
SQL> declare
2 v_query clob;
3 out_string varchar2(100);
4 cursor c_ids
5 is
6 select distinct product, count(distinct(product)) over () tot_rows from t order by 1 asc;
7 procedure print_clob_to_output (p_clob in clob)
8 is
9 l_offset pls_integer := 1;
10 l_chars pls_integer;
11 begin
12 loop
13 exit when l_offset > dbms_lob.getlength(p_clob);
14 l_chars := dbms_lob.instr(p_clob, chr(10), l_offset, 1);
15 if l_chars is null or l_chars = 0 then
16 l_chars := dbms_lob.getlength(p_clob) + 1;
17 end if;
18 dbms_output.put_line(dbms_lob.substr(p_clob, l_chars - l_offset, l_offset));
19 l_offset := l_chars + 1;
20 end loop;
21 end print_clob_to_output;
22 begin
23 dbms_output.enable(null);
24 for item in c_ids
25 loop
26 if item.tot_rows >= 1000
27 then
28 raise_application_error(-20001,'Maximum number of 1000 columns are not allowed',true);
29 end if;
30 out_string := item.product;
31 if c_ids%rowcount = 1
32 then
33 v_query := 'select * from (';
34 dbms_lob.append(v_query,''||chr(10)||'');
35 dbms_lob.append(v_query,' select * ');
36 dbms_lob.append(v_query,''||chr(10)||'');
37 dbms_lob.append(v_query,' from t ');
38 dbms_lob.append(v_query,''||chr(10)||'');
39 dbms_lob.append(v_query,' pivot( ');
40 dbms_lob.append(v_query,''||chr(10)||'');
41 dbms_lob.append(v_query,' count(product) ');
42 dbms_lob.append(v_query,''||chr(10)||'');
43 dbms_lob.append(v_query,' for product in ( '''||out_string||''' , ');
44 elsif c_ids%rowcount < item.tot_rows then
45 dbms_lob.append(v_query,''||chr(10)||'');
46 dbms_lob.append(v_query,' '''||out_string||''' ,');
47 else
48 dbms_lob.append(v_query,''||chr(10)||'');
49 dbms_lob.append(v_query,' '''||out_string||''' ) ');
50 end if;
51 end loop;
52 dbms_lob.append(v_query,''||chr(10)||'');
53 dbms_lob.append(v_query,' ) )');
54 print_clob_to_output(v_query);
55 end;
56 /
select * from (
select *
from t
pivot(
count(product)
for product in ( 'A' ,
'B' ,
'C' ,
'D' ,
'E' )
) )
PL/SQL procedure successfully completed.
SQL> select * from (
select *
from t
pivot(
count(product)
for product in ( 'A' ,
'B' ,
'C' ,
'D' ,
'E' )
) ) ;
CLID 'A' 'B' 'C' 'D' 'E'
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 0 0 0
2 1 0 1 0 0
4 0 0 0 1 1
5 0 1 1 1 1
3 1 0 1 0 0
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;
Divide the sum of grades using pivot operator
Each student may have two marks in each subject?
Make sure of that first of all
Try to use the average AVG(Grades)
SELECT
Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
(SELECT Student, Grades, Subject
FROM Grade_Report) AS SOURCETABLE
PIVOT
(AVG(Grades)
FOR Subject IN ([English], [Mathematics], [Science], [Programming],
[History])
)
Oracle SQL pivot query
Oracle 9i+ supports:
SELECT SUM(CASE WHEN t.month = 1 THEN t.value ELSE 0 END) AS JAN,
SUM(CASE WHEN t.month = 2 THEN t.value ELSE 0 END) AS FEB,
SUM(CASE WHEN t.month = 3 THEN t.value ELSE 0 END) AS MAR,
SUM(CASE WHEN t.month = 4 THEN t.value ELSE 0 END) AS APR,
SUM(CASE WHEN t.month = 5 THEN t.value ELSE 0 END) AS MAY,
SUM(CASE WHEN t.month = 6 THEN t.value ELSE 0 END) AS JUN
FROM YOUR_TABLE t
You only list two columns -- something like this should probably be grouped by year.
There is ANSI PIVOT (and UNPIVOT) syntax, but Oracle didn't support it until 11g. Prior to 9i, you'd have to replace the CASE statements with Oracle specific DECODE.
Rows to columns using PIVOT function (Oracle)
You were nearly done - the only missing point is - you should replace the MAX aggregate function with the LISTAGG function using the full syntax in the PIVOT clause.
Additionaly I adjusted the pivot column names to get nice names (without apostrophes).
See example below:
select * from (
select ID,
SUBJECT,GRADE
from tab
)
pivot
(
LISTAGG(GRADE,',') within group (order by GRADE)
for SUBJECT in
('MTH111' as MTH111,
'WRI001' as WRI001,
'PHY104' as PHY104)
)
Result as expected
ID MTH111 WRI001 PHY104
------ ---------- ---------- ----------
000442 B,W,W C- C,W
How can I summarize / pivot data with oracle sql
If your Oracle version is 11.1 or higher (which it should be if you are a relatively new user!) then you can use the PIVOT operator, as shown below.
Note that the result of the PIVOT operation can be given an alias (I used p) - this makes it easier to write the SELECT clause.
I assumed the name of your table is geological_data - replace it with your actual table name.
select p.*
, open_pit_tonnage + underground_tonnage as combined_tonnage
, open_pit_au_gt + underground_au_gt as combined_au_gt
, open_pit_au_oz + underground_au_oz as combined_au_oz
from geological_data
pivot (sum(tonnage) as tonnage, sum(au_gt) as au_gt, sum(au_oz) as au_oz
for area in ('Open Pit' as open_pit, 'Underground' as underground)) p
;
Related Topics
Oracle: Function Based Index Selective Uniqueness
How to Bulk Update Sequence Id Postgresql for All Tables
Adding Constraints That Check a Separate (Linked) Table for a Value
How to Return Two Columns with Function
In Postgresql, Force Unique on Combination of Two Columns
How to Remove the First Characters of a Specific Column in a Table
SQL Server Stored Procedure Return a Table
Mysql: How to Determine Foreign Key Relationships Programmatically
Oracle: SQL Query to Find All the Triggers Belonging to the Tables
Select Closest Numerical Value with MySQL Query
Pivot Dynamically, Returned Results from Join of Two Tables
Join a Count Query on Generate_Series() and Retrieve Null Values as '0'
Returning Result Even for Elements in In List That Don't Exist in Table
How to Build a Summary by Joining to a Single Table with SQL Server
Understanding How Join Works When 3 or More Tables Are Involved. [Sql]