sqloracle-databasepivotoracle12c
Dynamic Pivot Usage in Oracle Query
Number of rows in table = number of columns
I want to group each row and convert each characteristic value into a column.
I don't want to manually add pivot column values or use XML data. I have to write how I made them. Because it might be useful for understanding.
First query:
SELECT So.Order_No,
So.Release_No,
So.Sequence_No,
So.Part_No,
Csv.Characteristic_Id,
Csv.Characteristic_Id || '-' || Config_Characteristic_Api.Get_Description(Csv.Characteristic_Id) Characteristic_Desc,
Csv.Characteristic_Value,
Csv.Characteristic_Value || ' - ' || Config_Option_Value_Api.Get_Description(Csv.Characteristic_Id, Csv.Characteristic_Value) Characteristic_Value_Desc
FROM Shop_Ord So, Config_Spec_Value Csv
WHERE So.Part_No = Csv.Part_No AND So.Configuration_Id = Csv.Configuration_Id AND So.Configuration_Id != '*' AND So.Need_Date > '01.01.2019' AND
So.Part_No LIKE 'XL%'
ORDER BY So.Order_No, Csv.Characteristic_Value;
Result:
ORDER_NO RELEASE_NO SEQUENCE_NO PART_NO CHARACTERISTIC_ID CHARACTERISTIC_DESC CHARACTERISTIC_VALUE CHARACTERISTIC_VALUE_DESC
--------- ----------- ----------- -------- ------------------ ------------------------------ --------------------- ----------------------------
E1196 1 1 XL106 KK-001 KK-001-Abkant Kontrol Ünitesi KK-001-002 KK-001-002 - Esa S 630 CNC 2D Grafik Ekran - Dokunmatik
E1196 1 1 XL106 KK-005 KK-005-Elektrik Sistemi KK-005-002 KK-005-002 - 380 V-660 V, 50-60 Hz, 3 Ph
E1196 1 1 XL106 KK-006 KK-006-Elektrik Panosu Soğutma Sistemi KK-006-001 KK-006-001 - Fanlı
E1196 1 1 XL106 KK-008 KK-008-Abkant Ön Destek Sayısı KK-008-001 KK-008-001 - Standart
...
PIVOT:
WITH Pivot_ AS
(SELECT So.Order_No,
So.Release_No,
So.Sequence_No,
So.Part_No,
Csv.Characteristic_Id,
Csv.Characteristic_Value
FROM Shop_Ord So, Config_Spec_Value Csv
WHERE So.Part_No = Csv.Part_No AND So.Configuration_Id = Csv.Configuration_Id
AND So.Configuration_Id != '*' AND So.Need_Date > '01.01.2019' AND So.Part_No LIKE 'XL%'
ORDER BY So.Order_No)
SELECT *
FROM Pivot_
Pivot (MAX(Characteristic_Value) FOR(Characteristic_Id) IN('KK-001', 'KK-002'));
Result:
ORDER_NO RELEASE_NO SEQUENCE_NO PART_NO 'KK-001' 'KK-002'
--------- --------- ------------ ------- --------- ---------
E1196 1 1 XL106 KK-001-002 00
E1334 1 1 XL107 KK-001-002 00
E1379 1 1 XL106 KK-001-002 KK-002-001
E1470 2 1 XL107 KK-001-002 KK-002-001
...
PIVOT XML:
WITH Pivot_ AS
(SELECT So.Order_No, So.Release_No, So.Sequence_No, So.Part_No, Csv.Characteristic_Id, Csv.Characteristic_Value
FROM Shop_Ord So, Config_Spec_Value Csv
WHERE So.Part_No = Csv.Part_No AND So.Configuration_Id = Csv.Configuration_Id AND So.Configuration_Id != '*' AND So.Need_Date > '01.01.2019' AND
So.Part_No LIKE 'XL%'
ORDER BY So.Order_No)
SELECT *
FROM Pivot_
Pivot Xml (MAX(Characteristic_Value) FOR(Characteristic_Id) IN (SELECT Cs_.Characteristic_Id
FROM Config_Spec_Value Cs_
WHERE Cs_.Part_No = '1065821'
GROUP BY Cs_.Characteristic_Id));
Result:
ORDER_NO RELEASE_NO SEQUENCE_NO PART_NO CHARACTERISTIC_ID_XML
---------- ---------- ----------- --------- ------------
*1642 1 1 XL106 <XMLTYPE>
*1643 1 1 XL106 <XMLTYPE>
*1644 1 1 XL106 <XMLTYPE>
...
I want to see the columns directly after all on XML Pivot.
ORDER_NO RELEASE_NO SEQUENCE_NO PART_NO KK-001 KK-002 KK-003 ....
---------- ---------- ----------- --------- --------- --------- --------- ---------
*1642 1 1 XL106 KK-001-01 NULL NULL ...
*1643 1 1 XL106 KK-001-04 KK-002-00 KK-003-08 ...
*1644 1 1 XL106 KK-001-02 KK-002-10 NULL ...
...
Best regards.
Solution
I found a solution to this problem like this. And i want to share. Since it has been a long time since my question, I can explain it through a different example.
CREATE OR REPLACE TYPE pivotimpl AS OBJECT
(
ret_type anytype, -- The return type of the table function
stmt VARCHAR2(32767),
fmt VARCHAR2(32767),
cur INTEGER,
STATIC FUNCTION odcitabledescribe
(
rtype OUT anytype,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER := 0
) RETURN NUMBER,
STATIC FUNCTION odcitableprepare
(
sctx OUT pivotimpl,
ti IN sys.odcitabfuncinfo,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER := 0
) RETURN NUMBER,
STATIC FUNCTION odcitablestart
(
sctx IN OUT pivotimpl,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER := 0
) RETURN NUMBER,
MEMBER FUNCTION odcitablefetch
(
SELF IN OUT pivotimpl,
nrows IN NUMBER,
outset OUT anydataset
) RETURN NUMBER,
MEMBER FUNCTION odcitableclose(SELF IN pivotimpl) RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY pivotimpl AS
STATIC FUNCTION odcitabledescribe
(
rtype OUT anytype,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER
) RETURN NUMBER IS
atyp anytype;
cur INTEGER;
numcols NUMBER;
desc_tab dbms_sql.desc_tab2;
rc SYS_REFCURSOR;
t_c2 VARCHAR2(32767);
t_fmt VARCHAR2(1000);
BEGIN
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, p_stmt, dbms_sql.native);
dbms_sql.describe_columns2(cur, numcols, desc_tab);
dbms_sql.close_cursor(cur);
--
anytype.begincreate(dbms_types.typecode_object, atyp);
FOR i IN 1 .. numcols - 2
LOOP
atyp.addattr(desc_tab(i).col_name,
CASE desc_tab(i).col_type
WHEN 1 THEN
dbms_types.typecode_varchar2
WHEN 2 THEN
dbms_types.typecode_number
WHEN 9 THEN
dbms_types.typecode_varchar2
WHEN 11 THEN
dbms_types.typecode_varchar2 -- show rowid as varchar2
WHEN 12 THEN
dbms_types.typecode_date
WHEN 208 THEN
dbms_types.typecode_varchar2 -- show urowid as varchar2
WHEN 96 THEN
dbms_types.typecode_char
WHEN 180 THEN
dbms_types.typecode_timestamp
WHEN 181 THEN
dbms_types.typecode_timestamp_tz
WHEN 231 THEN
dbms_types.typecode_timestamp_ltz
WHEN 182 THEN
dbms_types.typecode_interval_ym
WHEN 183 THEN
dbms_types.typecode_interval_ds
END, desc_tab(i).col_precision, desc_tab(i).col_scale,
CASE desc_tab(i).col_type
WHEN 11 THEN
18 -- for rowid col_max_len = 16, and 18 characters are shown
ELSE
desc_tab(i).col_max_len
END, desc_tab(i).col_charsetid, desc_tab(i).col_charsetform);
END LOOP;
IF instr(p_fmt, '@p@') > 0 THEN
t_fmt := p_fmt;
ELSE
t_fmt := '@p@';
END IF;
OPEN rc FOR REPLACE('select distinct ' || t_fmt || '
from( ' || p_stmt || ' )
order by ' || t_fmt, '@p@', desc_tab(numcols - 1).col_name);
LOOP
FETCH rc
INTO t_c2;
EXIT WHEN rc%NOTFOUND;
atyp.addattr(t_c2,
CASE desc_tab(numcols).col_type WHEN 1 THEN dbms_types.typecode_varchar2 WHEN 2 THEN dbms_types.typecode_number WHEN 9 THEN
dbms_types.typecode_varchar2 WHEN 11 THEN dbms_types.typecode_varchar2 -- show rowid as varchar2
WHEN 12 THEN dbms_types.typecode_date WHEN 208 THEN dbms_types.typecode_urowid WHEN 96 THEN dbms_types.typecode_char WHEN 180 THEN
dbms_types.typecode_timestamp WHEN 181 THEN dbms_types.typecode_timestamp_tz WHEN 231 THEN dbms_types.typecode_timestamp_ltz WHEN 182 THEN
dbms_types.typecode_interval_ym WHEN 183 THEN dbms_types.typecode_interval_ds END, desc_tab(numcols).col_precision,
desc_tab(numcols).col_scale,
CASE desc_tab(numcols).col_type WHEN 11 THEN 18 -- for rowid col_max_len = 16, and 18 characters are shown
ELSE desc_tab(numcols).col_max_len END, desc_tab(numcols).col_charsetid, desc_tab(numcols).col_charsetform);
END LOOP;
CLOSE rc;
atyp.endcreate;
anytype.begincreate(dbms_types.typecode_table, rtype);
rtype.setinfo(NULL, NULL, NULL, NULL, NULL, atyp, dbms_types.typecode_object, 0);
rtype.endcreate();
RETURN odciconst.success;
EXCEPTION
WHEN OTHERS THEN
RETURN odciconst.error;
END;
--
STATIC FUNCTION odcitableprepare
(
sctx OUT pivotimpl,
ti IN sys.odcitabfuncinfo,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER
) RETURN NUMBER IS
prec PLS_INTEGER;
scale PLS_INTEGER;
len PLS_INTEGER;
csid PLS_INTEGER;
csfrm PLS_INTEGER;
elem_typ anytype;
aname VARCHAR2(30);
tc PLS_INTEGER;
BEGIN
tc := ti.rettype.getattreleminfo(1, prec, scale, len, csid, csfrm, elem_typ, aname);
--
IF instr(p_fmt, '@p@') > 0 THEN
sctx := pivotimpl(elem_typ, p_stmt, p_fmt, NULL);
ELSE
sctx := pivotimpl(elem_typ, p_stmt, '@p@', NULL);
END IF;
RETURN odciconst.success;
END;
--
STATIC FUNCTION odcitablestart
(
sctx IN OUT pivotimpl,
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER
) RETURN NUMBER IS
cur INTEGER;
numcols NUMBER;
desc_tab dbms_sql.desc_tab2;
t_stmt VARCHAR2(32767);
type_code PLS_INTEGER;
prec PLS_INTEGER;
scale PLS_INTEGER;
len PLS_INTEGER;
csid PLS_INTEGER;
csfrm PLS_INTEGER;
schema_name VARCHAR2(30);
type_name VARCHAR2(30);
version VARCHAR2(30);
attr_count PLS_INTEGER;
attr_type anytype;
attr_name VARCHAR2(100);
dummy2 INTEGER;
BEGIN
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, p_stmt, dbms_sql.native);
dbms_sql.describe_columns2(cur, numcols, desc_tab);
dbms_sql.close_cursor(cur);
--
FOR i IN 1 .. numcols - 2
LOOP
t_stmt := t_stmt || ', "' || desc_tab(i).col_name || '"';
END LOOP;
--
type_code := sctx.ret_type.getinfo(prec, scale, len, csid, csfrm, schema_name, type_name, version, attr_count);
FOR i IN numcols - 1 .. attr_count
LOOP
type_code := sctx.ret_type.getattreleminfo(i, prec, scale, len, csid, csfrm, attr_type, attr_name);
t_stmt := t_stmt || REPLACE(', max( decode( ' || sctx.fmt || ', ''' || attr_name || ''', ' || desc_tab(numcols).col_name || ' ) )', '@p@',
desc_tab(numcols - 1).col_name);
END LOOP;
t_stmt := 'select ' || substr(t_stmt, 2) || ' from ( ' || sctx.stmt || ' )';
FOR i IN 1 .. numcols - 2
LOOP
IF i = 1 THEN
t_stmt := t_stmt || ' group by "' || desc_tab(i).col_name || '"';
ELSE
t_stmt := t_stmt || ', "' || desc_tab(i).col_name || '"';
END IF;
END LOOP;
--
dbms_output.put_line(t_stmt);
sctx.cur := dbms_sql.open_cursor;
dbms_sql.parse(sctx.cur, t_stmt, dbms_sql.native);
FOR i IN 1 .. attr_count
LOOP
type_code := sctx.ret_type.getattreleminfo(i, prec, scale, len, csid, csfrm, attr_type, attr_name);
CASE type_code
WHEN dbms_types.typecode_char THEN
dbms_sql.define_column(sctx.cur, i, 'x', 32767);
WHEN dbms_types.typecode_varchar2 THEN
dbms_sql.define_column(sctx.cur, i, 'x', 32767);
WHEN dbms_types.typecode_number THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS NUMBER));
WHEN dbms_types.typecode_date THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS DATE));
WHEN dbms_types.typecode_urowid THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS UROWID));
WHEN dbms_types.typecode_timestamp THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS TIMESTAMP));
WHEN dbms_types.typecode_timestamp_tz THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS TIMESTAMP WITH TIME ZONE));
WHEN dbms_types.typecode_timestamp_ltz THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS TIMESTAMP WITH LOCAL TIME ZONE));
WHEN dbms_types.typecode_interval_ym THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS INTERVAL YEAR TO MONTH));
WHEN dbms_types.typecode_interval_ds THEN
dbms_sql.define_column(sctx.cur, i, CAST(NULL AS INTERVAL DAY TO SECOND));
END CASE;
END LOOP;
dummy2 := dbms_sql.execute(sctx.cur);
RETURN odciconst.success;
END;
--
MEMBER FUNCTION odcitablefetch
(
SELF IN OUT pivotimpl,
nrows IN NUMBER,
outset OUT anydataset
) RETURN NUMBER IS
c1_col_type PLS_INTEGER;
type_code PLS_INTEGER;
prec PLS_INTEGER;
scale PLS_INTEGER;
len PLS_INTEGER;
csid PLS_INTEGER;
csfrm PLS_INTEGER;
schema_name VARCHAR2(30);
type_name VARCHAR2(30);
version VARCHAR2(30);
attr_count PLS_INTEGER;
attr_type anytype;
attr_name VARCHAR2(100);
v1 VARCHAR2(32767);
n1 NUMBER;
d1 DATE;
ur1 UROWID;
ids1 INTERVAL DAY TO SECOND;
iym1 INTERVAL YEAR TO MONTH;
ts1 TIMESTAMP;
tstz1 TIMESTAMP WITH TIME ZONE;
tsltz1 TIMESTAMP WITH LOCAL TIME ZONE;
BEGIN
outset := NULL;
IF nrows < 1 THEN
-- is this possible???
RETURN odciconst.success;
END IF;
--
dbms_output.put_line('fetch');
IF dbms_sql.fetch_rows(self.cur) = 0 THEN
RETURN odciconst.success;
END IF;
--
dbms_output.put_line('done');
type_code := self.ret_type.getinfo(prec, scale, len, csid, csfrm, schema_name, type_name, version, attr_count);
anydataset.begincreate(dbms_types.typecode_object, self.ret_type, outset);
outset.addinstance;
outset.piecewise();
FOR i IN 1 .. attr_count
LOOP
type_code := self.ret_type.getattreleminfo(i, prec, scale, len, csid, csfrm, attr_type, attr_name);
dbms_output.put_line(attr_name);
CASE type_code
WHEN dbms_types.typecode_char THEN
dbms_sql.column_value(self.cur, i, v1);
outset.setchar(v1);
WHEN dbms_types.typecode_varchar2 THEN
dbms_sql.column_value(self.cur, i, v1);
outset.setvarchar2(v1);
WHEN dbms_types.typecode_number THEN
dbms_sql.column_value(self.cur, i, n1);
outset.setnumber(n1);
WHEN dbms_types.typecode_date THEN
dbms_sql.column_value(self.cur, i, d1);
outset.setdate(d1);
WHEN dbms_types.typecode_urowid THEN
dbms_sql.column_value(self.cur, i, ur1);
outset.seturowid(ur1);
WHEN dbms_types.typecode_interval_ds THEN
dbms_sql.column_value(self.cur, i, ids1);
outset.setintervalds(ids1);
WHEN dbms_types.typecode_interval_ym THEN
dbms_sql.column_value(self.cur, i, iym1);
outset.setintervalym(iym1);
WHEN dbms_types.typecode_timestamp THEN
dbms_sql.column_value(self.cur, i, ts1);
outset.settimestamp(ts1);
WHEN dbms_types.typecode_timestamp_tz THEN
dbms_sql.column_value(self.cur, i, tstz1);
outset.settimestamptz(tstz1);
WHEN dbms_types.typecode_timestamp_ltz THEN
dbms_sql.column_value(self.cur, i, tsltz1);
outset.settimestampltz(tsltz1);
END CASE;
END LOOP;
outset.endcreate;
RETURN odciconst.success;
END;
--
MEMBER FUNCTION odcitableclose(SELF IN pivotimpl) RETURN NUMBER IS
c INTEGER;
BEGIN
c := self.cur;
dbms_sql.close_cursor(c);
RETURN odciconst.success;
END;
END;
CREATE OR REPLACE FUNCTION pivot
(
p_stmt IN VARCHAR2,
p_fmt IN VARCHAR2 := 'upper(@p@)',
dummy IN NUMBER := 0
) RETURN anydataset
PIPELINED USING pivotimpl;
--grant execute on pivot to '&USER';
SELECT results.*
FROM (TABLE(pivot('SELECT sc.st_id,
sc.full_name,
sc.lesson_name,
MAX(sc.score) price
FROM (SELECT ss.st_id,
student_api.get_full_name(ss.st_id),
replace(trim(substr(upper(lesson_api.get_name(ss.lesson_name)), 0, 15)), '' '', ''_'') lesson_name,
ss.score
FROM student_score ss
WHERE ss.st_id like ''&st_id_'' AND ss.score IS NOT NULL) sc
GROUP BY sc.st_id,
sc.full_name,
sc.lesson_name
ORDER BY sc.st_id'))) results;
Resutls:
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?