0

I write some pivot query in oracle but I have some problem with order data :

   Select * FROM
(
SELECT
    ve_nazwisko PH,
    odbiorca,
    CONCAT(CONCAT(nazwa1,' '),nazwa2) FIRMA,
    segment2017,
    segment2018,
    symbol_kraju,
    nazwa_kraju, 
    netto,
    zysk,
    CONCAT(CONCAT(miesiac,' '),ROK) Data
FROM
    v_eksport_arkusz_4_6
    WHERE ROK >= '2017'
)
PIVOT
(
SUM(netto) as netto,
SUM(zysk) as zysk 
FOR Data
IN('01 Sty 2017','02 Lut 2017','03 Mar 2017','04 Kwi 2017','05 Maj 2017','06 Cze 2017','07 Lip 2017','08 Sie 2017','09 Wrz 2017','10 Paź 2017','11 Lis 2017','12 Gru 2017',
        '01 Sty 2018','02 Lut 2018','03 Mar 2018','04 Kwi 2018','05 Maj 2018','06 Cze 2018','07 Lip 2018','08 Sie 2018','09 Wrz 2018','10 Paź 2018','11 Lis 2018','12 Gru 2018',
        '01 Sty 2019','02 Lut 2019','03 Mar 2019','04 Kwi 2019','05 Maj 2019','06 Cze 2019','07 Lip 2019','08 Sie 2019','09 Wrz 2019','10 Paź 2019','11 Lis 2019','12 Gru 2019'
))

And displaying data they are not something I expected.

Now displaing this:

  • January 2017 - Netto,January 2017 Zysk,February 2017 Netto,February 2017 Zysk and something like this to 2019

enter image description here

I want display pivoting data in the following order : - January 2017 Netto,February 2017 Netto,March 2018 Netto and to last 2017 months next January 2017 Zysk ,February 2017 Zysk,March 2018 Zysk and to last 2017

And this data loop to 2019

Somthing like in pivot table in Excel :

enter image description here

1 Answer 1

0

If you want to retrieve columns in a specific order, then select columns in that specific order.

drop table pivot_test;
create table pivot_test
(
  c0 number,
  c1 varchar2(30 char),
  c2 number,
  c3 number
);

insert into pivot_test values (0, 'A', 1, 5);
insert into pivot_test values (0, 'B', 2, 5);
insert into pivot_test values (0, 'C', 3, 5);
insert into pivot_test values (0, 'D', 4, 5);
commit;

select * from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d)); 

        C0    A_NETTO     A_ZYSK    B_NETTO     B_ZYSK    C_NETTO     C_ZYSK    D_NETTO     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          5          2          5          3          5          4          5

select c0, a_netto, b_netto, c_netto, d_netto, a_zysk, b_zysk, c_zysk, d_zysk from pivot_test pivot (sum(c2) as netto, sum(c3) as zysk for c1 in ('A' as a, 'B' as b, 'C' as c, 'D' as d)); 


        C0    A_NETTO    B_NETTO    C_NETTO    D_NETTO     A_ZYSK     B_ZYSK     C_ZYSK     D_ZYSK
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         0          1          2          3          4          5          5          5          5

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.