Skip to Main Content
  • Questions

  • Multi-Group Unpivot in Oracle SQL returns incorrect number of rows

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Migs.

Asked: July 27, 2020 - 6:21 pm UTC

Last updated: July 28, 2020 - 1:55 pm UTC

Version: 12

Viewed 1000+ times

You Asked

I have the table below:

CREATE TABLE "XXSAMPLE2" 
 (
 "JG_INFO_V1" VARCHAR2(240 BYTE), 
 "JG_INFO_V14" VARCHAR2(150 BYTE), 
 "JG_INFO_V16" VARCHAR2(150 BYTE), 
 "JG_INFO_V21" VARCHAR2(150 BYTE), 
 "JG_INFO_V32" VARCHAR2(1996 BYTE), 
 "JG_INFO_N3" NUMBER, 
 "JG_INFO_N4" NUMBER, 
 "JG_INFO_N11" NUMBER, 
 "JG_INFO_N15" NUMBER, 
 "JG_INFO_N30" NUMBER 
 );

and the following records: 

Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',859634,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',421.42,50.57,110,13.2,'KOREKTA',859635,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',490.38,49.04,128,12.8,'KOREKTA',859635,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',122.6,6.13,32,1.6,'VAT',860621,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',860621,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-100,-10,-100,-10,'KOREKTA',859638,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',-87,-10.44,-87,-10.44,'KOREKTA',859638,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',40,4.4,40,4.4,'KOREKTA',859638,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-3225.79,-225.81,-842,-58.94,'VAT',860622,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-122.6,-6.13,-32,-1.6,'VAT',860622,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',367.79,40.46,96,10.56,'KOREKTA',860622,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',3225.79,322.58,842,84.2,'KOREKTA',860622,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863622,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',74,8.88,74,8.88,'KOREKTA',863622,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863625,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',74,8.14,74,8.14,'KOREKTA',863625,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',115.16,11.53,30.06,3.01,'KOREKTA',863626,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',283.5,34.02,74,8.88,'KOREKTA',863626,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-200,-14,-200,-14,'VAT',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-123,-8.61,-123,-8.61,'VAT',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-72,-3.6,-72,-3.6,'VAT',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',48,4.8,48,4.8,'KOREKTA',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',200,24,200,24,'KOREKTA',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',369,40.59,369,40.59,'KOREKTA',860619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('H','Krakowska',null,null,null,null,18,'AV',null,null);
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',100,11,100,11,'KOREKTA',859619,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',50,5.5,50,5.5,'KOREKTA',859624,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',144,17.28,144,17.28,'KOREKTA',859624,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-360.12,-36.01,-94,-9.4,'KOREKTA',859639,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',-122.6,-13.49,-32,-3.52,'KOREKTA',859639,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',114.93,13.79,30,3.6,'KOREKTA',859639,'EUR');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',100,7,100,7,'VAT',859618,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',72,3.6,72,3.6,'VAT',859621,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',123,8.61,123,8.61,'VAT',859621,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',200,14,200,14,'VAT',859621,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',32,1.6,32,1.6,'VAT',859626,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',842,58.94,842,58.94,'VAT',859626,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',110,13.2,110,13.2,'KOREKTA',859627,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',128,12.8,128,12.8,'KOREKTA',859627,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',32,1.6,32,1.6,'VAT',859628,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',842,58.94,842,58.94,'VAT',859628,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',110,13.2,110,13.2,'KOREKTA',859629,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',128,12.8,128,12.8,'KOREKTA',859629,'PLN');
 Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',122.6,6.13,32,1.6,'VAT',859634,'EUR');


And I'm currently using the query below:

Select  invoice_id
  , Invoice_Type
  , BOX
  ,   functional_box
  ,  ROUND(sum(ENTERED_AMT), 2)  ENTERED_AMT_TOT
  ,  ROUND(sum(FUNC_AMT),2)   FUNC_AMT_TOT            
 from (
 Select  jg_info_v14 box    -- taxable_box
  ,   Case When jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v14||'W' else null end functional_box
  ,  jg_info_n3  func_amt   -- taxable_amt
  ,  jg_info_n15 entered_amt  -- entered_taxable_amount
  , jg_info_v21 Invoice_Type
  , jg_info_n11 invoice_id
  ,   jg_info_v32 invoice_currency_code
 From  XXSAMPLE2
 WHERE  jg_info_v1 IN ('AR', 'AP')
 and  jg_info_v14 <> 'tns:'
 union all
 Select  jg_info_v16  box    -- tax_box
  ,   Case When jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v16||'W' else null end functional_box
  ,  jg_info_n4   func_amt   -- tax_amt
  ,  jg_info_n30  entered_amt  -- entered_tax_amount
  , jg_info_v21  Invoice_Type
  , jg_info_n11  invoice_id
  ,   jg_info_v32     invoice_currency_code
 From  XXSAMPLE2
 WHERE   jg_info_v1 IN ('AR', 'AP')
 and  jg_info_v16 <> 'tns:'
 ) tax_info
 WHERE    invoice_id = 859639
 group by 
   tax_info.invoice_id
 ,  tax_info.Invoice_Type
 ,  tax_info.BOX
 ,    tax_info.functional_box
 ;

and below are the results:

INVOICE_ID INVOICE_TYPE BOX   FUNCTIONAL_BOX ENTERED_AMT_TOT FUNC_AMT_TOT
 ---------- ------------ ----------- -------------- --------------- -------------
 859639  KOREKTA   tns:P_14_3 tns:P_14_3W     3.6    13.79
 859639  KOREKTA   tns:P_13_5        -32        -122.6
 859639  KOREKTA   tns:P_13_4        -94       -360.12
 859639  KOREKTA   tns:P_13_3         30     114.93
 859639  KOREKTA   tns:P_14_5 tns:P_14_5W      -3.52     -13.49


However, I would want to use `UNPIVOT` to have the below result:

 INVOICE_ID INVOICE_TYPE BOX   AMOUNT  
 ---------- ------------ ----------- ------------
 859639  KOREKTA   tns:P_14_3    3.6    
 859639  KOREKTA   tns:P_13_5    -32
 859639  KOREKTA   tns:P_13_4    -94
 859639  KOREKTA   tns:P_13_3     30
 859639  KOREKTA   tns:P_14_5     -3.52
 859639  KOREKTA   tns:P_14_3W     13.79
 859639  KOREKTA   tns:P_14_5W    -13.49


I tried the below query but i am getting incorrect values in the sum:

Select  distinct 
   jg_info_n11 invoice_id
  ,   jg_info_v21 invoice_type
  ,   jg_info_v32 currency_code
  ,   TRX_BOX
  ,   sum(func_amt) func_amt
  ,   sum(entered_amt) entered_amt
 From  XXSAMPLE2
 unpivot (TRX_BOX   FOR TRX_BOX_VALUES  IN (jg_info_v14, jg_info_v16))
 unpivot (func_amt   FOR func_amt_values  IN (jg_info_n3, jg_info_n4))
 unpivot (entered_amt FOR entered_amt_values IN (jg_info_n15, jg_info_n30))
 WHERE   jg_info_v1 IN ('AR', 'AP')
 and  TRX_BOX <> 'tns:'
 AND     jg_info_n11 = 859639
 group by jg_info_n11
  ,   jg_info_v21
  ,   jg_info_v32
  ,   TRX_BOX;

below are the results:
 INVOICE_ID INVOICE_TYPE CURRENCY_CODE TRX_BOX  FUNC_AMT ENTERED_AMT  
 ---------- ------------ -------------- ----------- ----------- ---------------
 859639  KOREKTA   EUR    tns:P_13_4  -792.26   -206.8
 859639  KOREKTA   EUR    tns:P_14_5  -272.18   -71.04
 859639  KOREKTA   EUR    tns:P_13_3   257.44     67.2
 859639  KOREKTA   EUR    tns:P_14_3   257.44     67.2
 859639  KOREKTA   EUR    tns:P_13_5  -272.18   -71.04


If i remove the aggregates, 20 records are being fetched, 4 times the expected results:
 INVOICE_ID INVOICE_TYPE CURRENCY_CODE TRX_BOX  FUNC_AMT ENTERED_AMT  
 ---------- ------------ -------------- ----------- ----------- ---------------
 859639  KOREKTA   EUR    tns:P_13_4 -36.01  -9.4
 859639  KOREKTA   EUR    tns:P_13_5 -122.6  -3.52
 859639  KOREKTA   EUR    tns:P_14_5 -13.49  -32
 859639  KOREKTA   EUR    tns:P_14_5 -13.49  -3.52
 859639  KOREKTA   EUR    tns:P_13_3 114.93  3.6
 859639  KOREKTA   EUR    tns:P_14_5 -122.6  -3.52
 859639  KOREKTA   EUR    tns:P_13_4 -36.01  -94
 859639  KOREKTA   EUR    tns:P_13_3 114.93  30
 859639  KOREKTA   EUR    tns:P_13_5 -122.6  -32
 859639  KOREKTA   EUR    tns:P_14_3 114.93  30
 859639  KOREKTA   EUR    tns:P_13_4 -360.12  -94
 859639  KOREKTA   EUR    tns:P_13_3 13.79  3.6
 859639  KOREKTA   EUR    tns:P_13_4 -360.12  -9.4
 859639  KOREKTA   EUR    tns:P_13_5 -13.49  -32
 859639  KOREKTA   EUR    tns:P_13_5 -13.49  -3.52
 859639  KOREKTA   EUR    tns:P_14_5 -122.6  -32
 859639  KOREKTA   EUR    tns:P_13_3 13.79  30
 859639  KOREKTA   EUR    tns:P_14_3 114.93  3.6
 859639  KOREKTA   EUR    tns:P_14_3 13.79  30
 859639  KOREKTA   EUR    tns:P_14_3 13.79  3.6

It seems it's doing a `Merge Join Cartesian` with itself:

Plan hash value: 2179312268
  
 -------------------------------------------------------------------------------------
 | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT         |          |    24 | 28632 |    17   (6)| 00:00:01 |
 |   1 |  HASH UNIQUE             |          |    24 | 28632 |    17   (6)| 00:00:01 |
 |*  2 |   VIEW                   |          |    24 | 28632 |    16   (0)| 00:00:01 |
 |   3 |    UNPIVOT               |          |       |       |            |          |
 |*  4 |     VIEW                 |          |    12 |   110K|     8   (0)| 00:00:01 |
 |   5 |      UNPIVOT             |          |       |       |            |          |
 |*  6 |       VIEW               |          |     6 | 56364 |     4   (0)| 00:00:01 |
 |   7 |        UNPIVOT           |          |       |       |            |          |
 |*  8 |         TABLE ACCESS FULL| XXSAMPLE2 |     3 | 28374 |     2   (0)| 00:00:01 |
 -------------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
    2 - filter("unpivot_view_025"."ENTERED_AMT" IS NOT NULL)
    4 - filter("unpivot_view_013"."FUNC_AMT" IS NOT NULL)
    6 - filter("unpivot_view_007"."TRX_BOX" IS NOT NULL AND 
      "unpivot_view_007"."TRX_BOX"<>'tns:')
    8 - filter(("XXSAMPLE2"."JG_INFO_V1"='AP' OR "XXSAMPLE2"."JG_INFO_V1"='AR') 
      AND "XXSAMPLE2"."JG_INFO_N11"=859639)
  
 Note
 -----
    - dynamic statistics used: dynamic sampling (level=2)


Do I need to Add more conditions to the `UNPIVOT`?

Same details are provided in the LiveSQL: https://livesql.oracle.com/apex/livesql/s/kfn5wxgtssrrnsfuslnap7det

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/kfn5wxgtssrrnsfuslnap7det

and Chris said...

When you have multiple sets of columns you want to unpivot, chaining unpivot operations multiplies rows in your results.

The trick is to provide a comma-separated list of the columns you want to appear in your results before FOR. And each group of source columns that will provide these values. For example:

  unpivot include nulls (
    ( trx_box, entered_amt_values, func_amt ) for cols in ( 
      ( jg_info_v14, jg_info_n15, jg_info_n3 ) as 'trx_box',
      ( jg_info_v16, jg_info_n30, jg_info_n4 ) as 'func_amt'
    )
  )


Which gives:

with rws as (
 select jg_info_v14 
  , jg_info_n3 
  , jg_info_n11 
  , jg_info_n4 
  , jg_info_n15
  , jg_info_v16   
  , jg_info_v21  
  , jg_info_n30 
  , jg_info_v32
 from   xxsample2
 where  jg_info_v1 IN ('AR', 'AP')
)
  select jg_info_n11,
         jg_info_v21,
         jg_info_v32,
         case
           when trx_box like 'tns:P_14%' and jg_info_v32 <> 'PLN' 
           then trx_box || 'W'
           else null
         end functional_box,
         entered_amt_values,
         func_amt
  from   rws
  unpivot include nulls (
    ( trx_box, entered_amt_values, func_amt ) for cols in ( 
      ( jg_info_v14, jg_info_n15, jg_info_n3 ) as 'trx_box',
      ( jg_info_v16, jg_info_n30, jg_info_n4 ) as 'func_amt'
    )
  )
  where  jg_info_n11 = 859639
  and    trx_box <> 'tns:';
   
JG_INFO_N11    JG_INFO_V21    JG_INFO_V32    FUNCTIONAL_BOX    ENTERED_AMT_VALUES     FUNC_AMT   
        859639 KOREKTA        EUR            <null>                              -94     -360.12 
        859639 KOREKTA        EUR            <null>                              -32      -122.6 
        859639 KOREKTA        EUR            tns:P_14_5W                       -3.52      -13.49 
        859639 KOREKTA        EUR            <null>                               30      114.93 
        859639 KOREKTA        EUR            tns:P_14_3W                         3.6       13.79


Read more about this at https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

AMAZING

Migs, July 28, 2020 - 12:33 pm UTC

Thank you so much! I've always wondered how the Syntax was for these types of Pivots/Unpivots. Thanks again!
Chris Saxon
July 28, 2020 - 1:55 pm UTC

Glad this helped
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.