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
Oracle Dynamic Pivoting
Using dynamic sql for a result where the columns are unknown at the time of executing is a bit of a hassle in Oracle compared to certain other RDMBS.
Because the record type for the output is yet unknown, it can't be defined beforehand.
In Oracle 11g, one way is to use a nameless procedure that generates a temporary table with the pivoted result.
Then select the results from that temporary table.
declare
v_sqlqry clob;
v_cols clob;
begin
-- Generating a string with a list of the unique names
select listagg(''''||CCL||''' as "'||CCL||'"', ', ') within group (order by CCL)
into v_cols
from
(
select distinct CCL
from tableA
);
-- drop the temporary table if it exists
EXECUTE IMMEDIATE 'DROP TABLE tmpPivotTableA';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
-- A dynamic SQL to create a temporary table
-- based on the results of the pivot
v_sqlqry := '
CREATE GLOBAL TEMPORARY TABLE tmpPivotTableA
ON COMMIT PRESERVE ROWS AS
SELECT *
FROM (SELECT ID, CCL, Flag FROM TableA) src
PIVOT (MAX(Flag) FOR (CCL) IN ('||v_cols||')) pvt';
-- dbms_output.Put_line(v_sqlqry); -- just to check how the sql looks like
execute immediate v_sqlqry;
end;
/
select * from tmpPivotTableA;
Returns:
ID adam john rob terry
-- ---- ---- --- -----
1 x x x
2 x
You can find a test on db<>fiddle here
In Oracle 11g, another cool trick (created by Anton Scheffer) to be used can be found in this blog. But you'll have to add the pivot function for it.
The source code can be found in this zip
After that the SQL can be as simple as this:
select * from
table(pivot('SELECT ID, CCL, Flag FROM TableA'));
You'll find a test on db<>fiddle here
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;
/
Pivot with Dynamic Columns in Oracle
SYS_REFCURSOR
within a stored function(eg. PL/SQL used instead of using SQL directly) might be used in order to get dynamically generated result set(eg. Dynamic Pivot). In this case, a string for Conditional Aggregation is generated :
CREATE OR REPLACE FUNCTION get_passengers_rs RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql VARCHAR2(32767);
v_str VARCHAR2(32767);
BEGIN
SELECT LISTAGG('MAX(CASE WHEN rn = '||lvl||' THEN age||''(''||passengers||'')'' END)
AS "Age'||lvl||'"' ,',') WITHIN GROUP (ORDER BY 0)
INTO v_str
FROM ( SELECT level AS lvl
FROM dual
CONNECT BY level <= (SELECT MAX(COUNT(*)) FROM t GROUP BY ID ) ) t;
v_sql :=
'SELECT ID, '|| v_str ||'
FROM
(
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
FROM t
)
GROUP BY ID';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;
I also added the names of passengers in order to distinguish each data well.
Then run the below code :
VAR rc REFCURSOR
EXEC :rc := get_passengers_rs;
PRINT rc
from SQL Developer's Command Line in order to see the expected result set.
Above code generates this SQL string(v_sql) for the currently existing data
SELECT ID, MAX(CASE WHEN rn = 1 THEN age||'('||passengers||')' END) AS "Age1",
MAX(CASE WHEN rn = 2 THEN age||'('||passengers||')' END) AS "Age2",
MAX(CASE WHEN rn = 3 THEN age||'('||passengers||')' END) AS "Age3"
FROM
(
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
FROM t
)
GROUP BY ID
which yields
ID Age1 Age2 Age3
123456 58(Marie) 65(Ben)
123458 32(Aaron) 18(Caroline) 37(Stephanie)
as result set.
SQL Pivot with dynamic values for IN CLAUSE
An option which uses a substitution variable:
SQL> set verify off
SQL> clear columns
columns cleared
SQL> column llist new_value slist
SQL> select listagg(ym, ', ') within group (order by ym) as llist
2 from (select distinct
3 chr(39) || year_month ||chr(39) || ' as "' || year_month ||'"' ym
4 from details
5 );
LLIST
--------------------------------------------------------------------------------
'2020-05' as "2020-05", '2020-06' as "2020-06"
SQL> select * from
2 (select lob, clevel, classification, year_month, ccount from details)
3 pivot (sum(ccount)
4 for year_month in (&slist));
LOB CLEVEL CLASSIFIC 2020-05 2020-06
----------------- ------ --------- ---------- ----------
Finance level3 statement 65
Wealth Management level2 lending 23
Mergers and Acqu level3 statement 356
SQL>
Its drawback is that it'll fail if LISTAGG
's result is longer than 4000 characters (but, then again, what will you do with a result that contains more than 500 months?).
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.
Related Topics
Why Do Null Values Come First When Ordering Desc in a Postgresql Query
Return Multiple Columns and Rows from a Function Postgresql Instead of Record
Split Words with a Capital Letter in SQL
Is There a Product Function Like There Is a Sum Function in Oracle SQL
Favourite Performance Tuning Tricks
Best Database Field Type for a Url
Mysql: How to Copy Rows, But Change a Few Fields
Postgresql Wildcard Like for Any of a List of Words
Postgresql - Fetch the Rows Which Have the Max Value for a Column in Each Group by Group
When Would I Use Xml Instead of SQL
How to Select Multiple Rows Filled with Constants
How to Write Update SQL with Table Alias in SQL Server 2008
What Are Projection and Selection
Postgres and Indexes on Foreign Keys and Primary Keys
What Is the Equivalent of 'Describe Table' in SQL Server
Aggregating (X,Y) Coordinate Point Clouds in Postgresql
Stored Procedure That Exports Data into CSV Files Only Exports to One File