Advice Using Pivot Table in Oracle
Once you get Oracle 11G there is a built-in PIVOT feature. Prior to that, you are restricted to using CASE (or DECODE) expressions. I have an article on how to automate doing that on my blog.
Create Pivot view in SQL from a SQL table
A stored function(or procedure) might be created in order to create a SQL for Dynamic Pivoting, and the result set is loaded into a variable of type SYS_REFCURSOR :
CREATE OR REPLACE FUNCTION Get_Categories_RS RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols_1 VARCHAR2(32767);
v_cols_2 VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
WITHIN GROUP ( ORDER BY "level" DESC )
INTO v_cols_1
FROM (
SELECT DISTINCT "level"
FROM temp
);
SELECT LISTAGG( 'MAX(CASE WHEN category = '''||category||''' THEN "'||"level"||'" END) AS "'||"level"||'_'||category||'"' , ',' )
WITHIN GROUP ( ORDER BY category, "level" DESC )
INTO v_cols_2
FROM (
SELECT DISTINCT "level", category
FROM temp
);
v_sql :=
'SELECT "set", '|| v_cols_2 ||'
FROM
(
SELECT *
FROM temp
PIVOT
(
MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
)
)
GROUP BY "set"
ORDER BY "set"';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
in which I used two levels of pivoting : the first is within the inner query involving PIVOT Clause, and the second is in the outer query having the conditional aggregation logic. Notice that the order of levels should be in the descending order( Z, Y, X ) within the expected result as conforming to the description.
And then invoke
VAR rc REFCURSOR
EXEC :rc := Get_Categories_RS;
PRINT rc
from SQL Developer's Command Line in order to get the result set
Btw, avoid using reserved keywords such as set and level as in your case. I needed to quote them in order to be able to use.
Most efficient (fast) way to create a pivot table in Oracle SQL
Two most obvious options are ... well, pivoting:
SQL> select *
2 from foo
3 pivot (
4 count(st)
5 for st in ('open' as open, 'wip' as wip, 'closed' as closed)
6 )
7 order by ts;
TS OPEN WIP CLOSED
---------- ---------- ---------- ----------
21.08.2022 0 1 2
22.08.2022 0 2 1
23.08.2022 2 1 0
SQL>
or conditional aggregation:
SQL> select ts,
2 sum(case when st = 'open' then 1 else 0 end) as open,
3 sum(case when st = 'wip' then 1 else 0 end) as wip,
4 sum(case when st = 'closed' then 1 else 0 end) as closed
5 from foo
6 group by ts
7 order by ts;
TS OPEN WIP CLOSED
---------- ---------- ---------- ----------
21.08.2022 0 1 2
22.08.2022 0 2 1
23.08.2022 2 1 0
SQL>
Is one better or worse than another (or something else), I can't tell - you'd have to do some test on real data.
Oracle SQL PIVOT Table
You can do something like this:
SQL Fiddle
Oracle 11g R2 Schema Setup:
CREATE TABLE tbl ( "mod", "floor", "Remaining Counts", CountType ) AS
SELECT 'dz-P-1A', 1, 37, 'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-1D', 1, 321, 'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-1T', 1, 16, 'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-2A', 2, 25, 'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-R-1T', 1, 3318, 'CycleCount' FROM DUAL
UNION ALL SELECT 'dz-P-1A', 1, 6351, 'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-P-1D', 1, 121, 'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-P-2A', 2, 12638, 'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1F', 1, 68, 'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1O', 1, 47, 'SimpleBinCount' FROM DUAL
UNION ALL SELECT 'dz-R-1T', 1, 2051, 'SimpleBinCount' FROM DUAL;
Query 1:
PIVOT using SUM( CASE ... ) statements:
SELECT CountType,
SUM( CASE "mod" WHEN 'dz-P-1A' THEN "Remaining Counts" END ) AS "P-1-A",
SUM( CASE "mod" WHEN 'dz-P-2A' THEN "Remaining Counts" END ) AS "P-2-A",
SUM( CASE "mod" WHEN 'dz-R-1T' THEN "Remaining Counts" END ) AS "R-1-T",
SUM( CASE "mod" WHEN 'dz-R-1F' THEN "Remaining Counts" END ) AS "R-1-F",
SUM( CASE "mod" WHEN 'dz-R-1O' THEN "Remaining Counts" END ) AS "R-1-O",
SUM( CASE "mod" WHEN 'dz-P-1B' THEN "Remaining Counts" END ) AS "P-1-B",
SUM( CASE "mod" WHEN 'dz-P-1D' THEN "Remaining Counts" END ) AS "P-1-D"
FROM tbl
GROUP BY CountType
Results:
| COUNTTYPE | P-1-A | P-2-A | R-1-T | R-1-F | R-1-O | P-1-B | P-1-D |
|----------------|-------|-------|-------|--------|--------|--------|-------|
| SimpleBinCount | 6351 | 12638 | 2051 | 68 | 47 | (null) | 121 |
| CycleCount | 37 | 25 | 3318 | (null) | (null) | (null) | 321 |
Query 2:
Using PIVOT transposition:
SELECT * FROM (
SELECT SUBSTR( "mod", 4 ) AS "mod",
"Remaining Counts",
CountType
FROM tbl t
)
PIVOT
(
SUM("Remaining Counts")
FOR "mod" IN ( 'P-1A', 'P-2A', 'R-1T', 'R-1F', 'R-1O', 'P-1B', 'P-1D' )
)
Results:
| COUNTTYPE | 'P-1A' | 'P-2A' | 'R-1T' | 'R-1F' | 'R-1O' | 'P-1B' | 'P-1D' |
|----------------|--------|--------|--------|--------|--------|--------|--------|
| SimpleBinCount | 6351 | 12638 | 2051 | 68 | 47 | (null) | 121 |
| CycleCount | 37 | 25 | 3318 | (null) | (null) | (null) | 321 |
Edit - Wrapping your query:
WITH qry AS (
select drop_zone_id as "Mod",
bin_level as "Floor",
icqa_process_properties.icqa_process_property_value as "Count Type",
count(*) as "Remaining Counts",
concat(drop_zone_id, icqa_process_properties.icqa_process_property_value) as "Unique",
to_char(sysdate,'hh:mi:ssam') as "Time Last Updated",
to_char(sysdate, 'MM-DD-YYYY') as "Date Last Updated"
from icqa_process_locations
inner join icqa_processes on icqa_processes.icqa_process_id = icqa_process_locations.icqa_process_id
inner join icqa_process_properties on icqa_processes.icqa_process_id = icqa_process_properties.icqa_process_id
inner join bins on bins.bin_id = icqa_process_locations.scannable_id
where icqa_count_attempt_id is NULL and icqa_processes.process_status = ('Active')
and drop_zone_id not like 'dz-R-1B' and drop_zone_id not like 'dz-P-1Z' and drop_zone_id not like 'dz-P-EACH_1'
and icqa_process_properties.icqa_process_property_value in ('CycleCount', 'SimpleBinCount')
group by icqa_process_properties.icqa_process_property_value, bin_level, drop_zone_id
order by icqa_process_properties.icqa_process_property_value, drop_zone_id
)
SELECT "Count Type",
SUM( CASE "Mod" WHEN 'dz-P-1A' THEN "Remaining Counts" END ) AS "P-1-A",
SUM( CASE "Mod" WHEN 'dz-P-2A' THEN "Remaining Counts" END ) AS "P-2-A",
SUM( CASE "Mod" WHEN 'dz-R-1T' THEN "Remaining Counts" END ) AS "R-1-T",
SUM( CASE "Mod" WHEN 'dz-R-1F' THEN "Remaining Counts" END ) AS "R-1-F",
SUM( CASE "Mod" WHEN 'dz-R-1O' THEN "Remaining Counts" END ) AS "R-1-O",
SUM( CASE "Mod" WHEN 'dz-P-1B' THEN "Remaining Counts" END ) AS "P-1-B",
SUM( CASE "Mod" WHEN 'dz-P-1D' THEN "Remaining Counts" END ) AS "P-1-D"
FROM qry
GROUP BY "Count Type";
Oracle create a Pivot table from 3 columns
You tagged Oracle18c so this should work for your version. I tested this on 11g.
SELECT *
FROM (
SELECT product_code, order_type, income
FROM Logistics
)
PIVOT (
sum(income)
for order_type
IN ('EB' AS EB, 'ER' AS ER, 'ES' AS ES, 'EK' AS EK)
);
This does require the set for the IN list to be filled in prior to execution. There is another syntax that allows a sub select but it returns XML. If you try to replace PIVOT XML with PIVOT it gives an error.
WITH orderTypes AS
(
select 'EB' as order_type from dual union all
select 'ER' as order_type from dual union all
select 'ES' as order_type from dual union all
select 'EK' as order_type from dual union all
select 'AA' as order_type from dual union all
select 'AB' as order_type from dual union all
select 'AC' as order_type from dual union all
select 'AD' as order_type from dual union all
select 'AE' as order_type from dual
)
SELECT *
FROM (
SELECT l.product_code, l.order_type, l.income
FROM Logistics l
)
PIVOT XML (
sum(income) AS orderSum
for order_type
IN ( select order_type from orderTypes)
);
Rotate/pivot table with aggregation in Oracle
Yes I think so. It is easy to do a pivot like this with a MAX aggregate:
SELECT
*
FROM
(
SELECT
project,
attribute,
value
FROM
table1
) AS SourceTable
PIVOT
(
MAX(value)
FOR attribute IN ([foo],[bar],[baz])
) AS pvt
Otherwise you have to do a case statement inside the a max aggregate. Like this:
SELECT
MAX(CASE WHEN attribute='foo' THEN value ELSE NULL END) AS foo,
MAX(CASE WHEN attribute='bar' THEN value ELSE NULL END) AS bar,
MAX(CASE WHEN attribute='baz' THEN value ELSE NULL END) AS baz,
project
FROM
table1
GROUP BY
project
This is almost the same thing as doing the PIVOT. But I would prefer doing the PIVOT over the CASE WHEN MAX..
Pivot in Oracle based on multiple columns
You can definitely use multiple columns within a PIVOT clause, here is an example using your table setup:
SELECT *
FROM demo
PIVOT (MAX(VALUE) FOR (identifier_1, identifier_2) IN ((3000, 23) AS A3000_23, (3000, 24) AS A3000_24,
(3001, 25) AS A3001_25, (3001, 26) AS A3001_26));
N.B.: Excuse the "A" in the column names, you need to start the identifier with a character, not a number.
Here is a DBFiddle showing the results (LINK)
Obviously you can see how this would quickly grow out-of-hand if you need to list large amounts of PIVOT columns.
Related Topics
How to Store a List in a Db Column
Sql: Capitalize First Letter Only
How to Group by Month from Date Field Using SQL
How to Get Other Columns When Using Spark Dataframe Groupby
Sqlplus Statement from Command Line
Discard Millisecond Part from Timestamp
How to Add 'On Delete Cascade' in Alter Table Statement
SQL Server 2008- Get Table Constraints
Is There a Difference Between Select * and Select [List Each Col]
Table or Column Name Cannot Start with Numeric
When to Use Except as Opposed to Not Exists in Transact SQL
Sql: How to Select Only Groups That Do Not Contain a Certain Value
How to Remove Extended Ascii Characters from a String in T-Sql
Why Does Varchar Need Length Specification
Postgres Error: More Than One Row Returned by a Subquery Used as an Expression