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.
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.
Dynamic Pivot in Oracle's SQL
You can't put a non constant string in the IN
clause of the pivot clause.
You can use Pivot XML for that.
From documentation:
subquery A subquery is used only in conjunction with the XML keyword.
When you specify a subquery, all values found by the subquery are used
for pivoting
It should look like this:
select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;
You can also have a subquery instead of the ANY
keyword:
select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;
Here is a sqlfiddle demo
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.
Use pivot for dynamically changing column headers using sql in oracle
What you can do
SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ('A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D")
)
WHERE ID = 120 AND app_id = 1
as a static pivot statement might be converted to a function which contains two respective parameters
CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type )
RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_cols VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''''||label||''' AS "'||label||'"' , ',' )
WITHIN GROUP ( ORDER BY label )
INTO v_cols
FROM ( SELECT DISTINCT label
FROM data
WHERE ID = i_id AND app_id = i_app_id );
v_sql :=
'SELECT *
FROM data
PIVOT
(
MAX(value) FOR label IN ( '|| v_cols ||' )
)
WHERE ID = :id AND app_id = :aid';
OPEN v_recordset FOR v_sql USING i_id, i_app_id;
RETURN v_recordset;
END;
/
in which an auxiliary query, in which the label
columns are distinctly selected, is used to determine the string(v_cols
for 'A' AS "A", 'B' AS "B",'C' AS "C",'D' AS "D"
) to be concatenated to the main SQL string in order to be used within the cursor which returns a value of type SYS_REFCURSOR
.
and is invoked by
VAR rc REFCURSOR
VAR v_id NUMBER
VAR v_app_id NUMBER
EXEC :rc := Get_Pivoted_Labels(:v_id,:v_app_id);
PRINT rc
from SQL developer's console.
Demonstration with generated SQL statements
If order of columns in the SELECT list matters, then use the code below in order to create the function
CREATE OR REPLACE FUNCTION Get_Pivoted_Labels( i_id data.id%type, i_app_id data.app_id%type )
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( ''''||label||''' AS "'||label||'"' , ',' )
WITHIN GROUP ( ORDER BY label ),
LISTAGG( label , ',' )
WITHIN GROUP ( ORDER BY label )
INTO v_cols_1, v_cols_2
FROM ( SELECT DISTINCT label, value
FROM data
WHERE ID = i_id AND app_id = i_app_id );
v_sql :=
'SELECT ID, '|| v_cols_2 ||', app_id
FROM data
PIVOT
(
MAX(value) FOR label IN ( '|| v_cols_1 ||' )
)
WHERE ID = :id AND app_id = :aid';
OPEN v_recordset FOR v_sql USING i_id, i_app_id;
RETURN v_recordset;
END;
/
Related Topics
SQL Transform Crosstab Pivot Data
Using Regular Expression Within a Stored Procedure
How to Parse Xml Tags in Bigquery Standard SQL
Get All Punch in and Out for Each Employee
Find Out the Calling Stored Procedure in SQL Server
SQL Trigger After Insert Update Another Table with Conditions
Transpose a Row into Columns with MySQL Without Using Unions
Generic SQL That Both Access and Odbc/Oracle Can Understand
Convert Delimited String to Rows in Oracle
Display SQL Custom Text from Table Column Result
Sql: Parse Comma-Delimited String and Use as Join
Ssms: How to Import (Copy/Paste) Data from Excel
Difference Between SQL Connection and Oledb Connection
Getting Extra Rows - After Joing the 3 Tables Using Left Join