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.
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
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
Fastest Way Merge Two SQLite Databases
How to Get Multiple Rows into One Line as a String
Isdate Function in SQL Evaluates Invalid Dates as Valid
Fifo Implementation in Inventory Using SQL
Boolean 'Not' in T-SQL Not Working on 'Bit' Datatype
How to Count Rows That Have the Same Values in Two Columns (Sql)
Concatenate/Merge Array Values During Grouping/Aggregation
Postgresql Not Ilike Clause Does Not Include Null String Values
Sequentially Number Rows by Keyed Group in SQL
Sqlite: Preventing Duplicate Rows
Modify Table: How to Change 'Allow Nulls' Attribute from Not Null to Allow Null
Alter Database Failed Because a Lock Could Not Be Placed on Database
SQL Left Join First Match Only
How to Execute a .SQL Script on Heroku
Why Does the SQLserver Optimizer Get So Confused with Parameters