How to apply pivot to result of query

Question

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:

numbers of accounts may be different;
name, code and data - one per query;
combination of accaunt+currency are unique;
result should be in one line;
total number of lines in result of query, cannot be more then 10 (in my example 8)

Hi Pavlo : I'm not sure PIVOT is going to work for you. This command is useful when you want to cross-tabulate a set of data. This means using data in a list to create a table-like structure - each cell contains the result of some aggregate function (SUM, COUNT etc). We need to know the number of columns in advance (SQL always needs to know the number of columns in advance). I am guessing that in your scenario name1 and name2 may have different numbers of accounts, and therefore columns - it's not going to work. What is your underlying requirement? — Christian Palmer, Feb 20 '17 at 14:00
I've added "id" column which numerates lines 1,2,3,...,8. And some scenario requirements. — Pavlo Plynko, Feb 20 '17 at 14:11
I've added an answer below. But I am very interested in why you want to change how the data is held. Why do you want the data in a single row? — Christian Palmer, Feb 20 '17 at 14:17
Some accounting program will build automatic reports based on query result. And this program accepts only one-line data. — Pavlo Plynko, Feb 20 '17 at 14:21
OK, then you're forced to try something. But Oracle will **always** want to know how many columns you're going to return in your query - there is no way around this restriction. — Christian Palmer, Feb 20 '17 at 14:28
If you don't know the number, name and order of **all** the columns in advance, you can't write a `SELECT` statement to get the result as you want it, no matter how complicated. You will need to use "dynamic SQL" which is a very different thing from standard SQL. — , Feb 20 '17 at 14:29
mmm, ok, total number of lines in result of query, cannot be more then 10 (8 in my example). Will it help? I've added this requirement to question. — Pavlo Plynko, Feb 20 '17 at 14:37
Can we for example reserve 10 columns for these "pivot" things, and when result is less then 10 - fill rest columns with ANY data ? — Pavlo Plynko, Feb 20 '17 at 14:50
Hi Pavlo - my answer will work - you just need to increase the columns from 8 to 10 — Christian Palmer, Feb 20 '17 at 16:55

Christian Palmer · Accepted Answer · 2017-02-21T08:24:47.333

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
;

I cannot run it with my tool (SQL Navigator 6.2), it gives "ORA-00928: missing SELECT keyword" after "AS" keyword. Which tool can I use to run this code? — Pavlo Plynko, Feb 20 '17 at 19:33
It might be a whitespace issue - get rid of the carriage returns so it reads AS ( SELECT That might help. I'm afraid I've never used SQL Navigator - I Use SQL Developer. — Christian Palmer, Feb 21 '17 at 08:16
I've updated the answer. The WITH clause is just there because I don't have your data in my system. If that's causing you problems just ignore it and use my updated SQL — Christian Palmer, Feb 21 '17 at 08:26
Really works! Thank you. I'm also going to try SQL Developer. — Pavlo Plynko, Feb 21 '17 at 08:37

score 0 · Answer 2 · answered Feb 20 '17 at 14:06

0

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)
    );

answered Feb 20 '17 at 14:06

RoundFour

347
1
7

Unfortunelly I don't know: each name has his own unique accounts. – Pavlo Plynko Feb 20 '17 at 14:17

score 0 · Answer 3 · answered Feb 22 '17 at 07:15

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

How to apply pivot to result of query

3 Answers3

Linked