CODEWITHSUNDEEP
sqloracle-databasepivot
Pavlo Plynko
Pavlo Plynko

Reputation: 586

How to apply pivot to result of query

There is my current query:

SELECT Name, Code, Today
    , Account || Currency as Accounts
FROM (
    SELECT
          b.description AS Name
        , b.contragentidentifycode AS Code
        , c.systemday AS Today
        , b.accountno AS Account
        , b.currencysname AS Currency
    FROM vAACCOUNT b, currentdaysetting c
    WHERE b.contragentid = 412
    AND b.accountno LIKE '26%' 
)

it gives me such result:

Name  | Code  | Today      | Accounts
---------------------------------------
name1 | code1 | 07.09.2016 | acc1+curr1
name1 | code1 | 07.09.2016 | acc2+curr1
name1 | code1 | 07.09.2016 | acc1+curr2       
name1 | code1 | 07.09.2016 | acc2+curr2       
name1 | code1 | 07.09.2016 | acc1+curr3            
name1 | code1 | 07.09.2016 | acc2+curr3            
name1 | code1 | 07.09.2016 | acc1+curr4
name1 | code1 | 07.09.2016 | acc2+curr4

I need convert this view to:

Name  | Code  | Today      | someName1  |  someName2  |  someName3  |  someName4  |  someName5  |  someName6  |  someName7  |  someName8
-------------------------------------------------------------------------------------------------------------------------------------------
name1 | code1 | 07.09.2016 | acc1+curr1 | acc2+curr1  | acc1+curr2  | acc2+curr2  | acc1+curr3  | acc2+curr3  | acc1+curr4  | acc2+curr4

I guess that most probably for this I have to use the keyword "Pivot". But all my attempts to do so - have failed. I can not to project what I see in the examples, to my table. Please help.

For number of columns I can add such "id" column:

SELECT id, Name, Code, Today
    , Account || Currency as Accounts
FROM (
    SELECT
         row_number() over (ORDER BY b.id) AS id
        , b.description AS Name
        ...

In my scenario:

Upvotes: 0

Views: 212

Answers (3)

Pavlo Plynko
Pavlo Plynko

Reputation: 586

There is my pivot solution:

SELECT *
FROM (
    SELECT id, Name, Code, Today, Account || Currency as Accounts
    FROM (
        SELECT
              row_number() over (ORDER BY b.id) AS id
            , b.description AS Name
            , b.contragentidentifycode AS Code
            , c.systemday AS Today
            , b.accountno AS Account
            , b.currencysname AS Currency
        FROM vAACCOUNT b, currentdaysetting c
        WHERE b.contragentid = 412
        AND b.accountno LIKE '26%' 
    )
)
pivot (
    MIN(Accounts)
    FOR ID  IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
) pvt

Upvotes: 0

Christian Palmer
Christian Palmer

Reputation: 1302

Per my comment above, I don't think PIVOT works for you. The answer from @RoundFour works, but requires that you know, and code for, all possible values for Account || Currency. This suggests there will never be new values for these items - I find that unlikely.

The following will allow you to switch the shape of your data. It makes no assumptions about the values in your data, but it does assume a limit on the number of possible combinations - I have coded for eight.

WITH account_data (name,code,today,account) 
AS
 (
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr1' FROM dual UNION ALL
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr1' FROM dual UNION ALL
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr2' FROM dual UNION ALL     
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr2' FROM dual UNION ALL       
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr3' FROM dual UNION ALL            
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr3' FROM dual UNION ALL            
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr4' FROM dual UNION ALL
 SELECT 'name1','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr4' FROM dual UNION ALL
 SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr1' FROM dual UNION ALL
 SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr1' FROM dual UNION ALL
 SELECT 'name2','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc1+curr2' FROM dual UNION ALL     
 SELECT 'name3','code1',TO_DATE('07.09.2016','DD.MM.YYYY'),'acc2+curr2' FROM dual 
 )
SELECT
 name
,code
,today
,MAX(account1)
,MAX(account2)
,MAX(account3)
,MAX(account4)
,MAX(account5)
,MAX(account6)
,MAX(account7)
,MAX(account8)
FROM
 (SELECT 
   name
  ,code
  ,today
  ,CASE
    WHEN rn = 1 THEN account
   END                             account1
  ,CASE
    WHEN rn = 2 THEN account
   END                             account2
  ,CASE
    WHEN rn = 3 THEN account
   END                             account3
  ,CASE
    WHEN rn = 4 THEN account
   END                             account4
  ,CASE
    WHEN rn = 5 THEN account
   END                             account5
  ,CASE
    WHEN rn = 6 THEN account
   END                             account6
  ,CASE
    WHEN rn = 7 THEN account
   END                             account7
  ,CASE
    WHEN rn = 8 THEN account
   END                             account8
  FROM
   (SELECT
    name 
   ,code
   ,today
   ,account
   ,ROW_NUMBER() OVER (PARTITION BY name ORDER BY account)   rn
   FROM
    account_data
   )
  )
GROUP BY
 name
,code
,today
;

UPDATE >>>>>>>>>

The WITH... clause above is just because I don't have your tables and data in my system. I've rewritten my answer using your query as a guide - please note I have not been able to test this ...

SELECT
 name
,code
,today
,MAX(account1)
,MAX(account2)
,MAX(account3)
,MAX(account4)
,MAX(account5)
,MAX(account6)
,MAX(account7)
,MAX(account8)
FROM
 (SELECT 
   name
  ,code
  ,today
  ,CASE
    WHEN rn = 1 THEN account
   END                             account1
  ,CASE
    WHEN rn = 2 THEN account
   END                             account2
  ,CASE
    WHEN rn = 3 THEN account
   END                             account3
  ,CASE
    WHEN rn = 4 THEN account
   END                             account4
  ,CASE
    WHEN rn = 5 THEN account
   END                             account5
  ,CASE
    WHEN rn = 6 THEN account
   END                             account6
  ,CASE
    WHEN rn = 7 THEN account
   END                             account7
  ,CASE
    WHEN rn = 8 THEN account
   END                             account8
  FROM
   (SELECT
     b.description AS Name
    ,b.contragentidentifycode AS Code
    ,c.systemday AS Today
    ,b.accountno AS Account
    ,b.currencysname AS Currency
    ,b.accountno || b.currencysname AS Accounts
    ,ROW_NUMBER() OVER (PARTITION BY b.description ORDER BY b.accountno)   rn
    FROM vAACCOUNT b, currentdaysetting c
    WHERE b.contragentid = 412
    AND b.accountno LIKE '26%' 
   )
  )
GROUP BY
 name
,code
,today
;

Upvotes: 1

RoundFour
RoundFour

Reputation: 347

If you know all the account+currency combinations you can use this pivot (I only implemented 3 of them here):

select *
from (
    <your-query>    )
pivot  (
    min(accounts) as accounts FOR (accounts) in ('acc1+curr1' as a, 'acc2+curr1' as b, 'acc1+curr2' c)
    );

Upvotes: 0

Related Questions