Need row data column wise in oracle

Question

I need to compare prices coming from 3 sources (27, 2, 55) from owners column. i am not sure if i should use select sub query or left join ?

Desired Result

Please, check this articles: [Why not upload images of code/errors when asking a question?](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question#285557) [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) and [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users). — astentx, Jun 27 '21 at 16:35
Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Jun 27 '21 at 16:36

score 0 · Answer 1 · answered Jun 27 '21 at 16:12

0

Looks like a pivot.

Sample data in lines #1 - 10; query you might need begins at line #12.

SQL> with test (id_value, as_of, timezone, price, owner) as
  2    -- sample data
  3    (select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 55 from dual union all
  4     select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 27 from dual union all
  5     select 'EEM.A', date '2021-06-25', 'J1530', 55.04,  2 from dual union all
  6     --
  7     select 'AMX.N', date '2021-06-25', 'J1530', 15.4, 55 from dual union all
  8     select 'AMX.N', date '2021-06-25', 'J1530', 15.4, 27 from dual union all
  9     select 'AMX.N', date '2021-06-25', 'J1530', 15.4,  2 from dual
 10    )
 11  -- query begins here
 12  select *
 13  from test
 14  pivot (max(price)
 15         for owner in (55, 27, 2)
 16        );

ID_VA AS_OF      TIMEZ         55         27          2
----- ---------- ----- ---------- ---------- ----------
AMX.N 06/25/2021 J1530       15,4       15,4       15,4
EEM.A 06/25/2021 J1530      55,04      55,04      55,04

SQL>

answered Jun 27 '21 at 16:12

Littlefoot

131,892
15
35
57

i just tried this on golden 6 software, do i need to start off from the first line - with test ? – Naina Jun 27 '21 at 16:23
Never hear of "golden 6 software", but - no, you'd start off from line #12 (as I wrote, but you either didn't read or didn't understand what I was saying). – Littlefoot Jun 27 '21 at 16:25
select * from quotes_mxsequities pivot (max(price) for owner in (55, 27, 2) ) where timezone = 'J1530' and asof = '25Jun2021'; – Naina Jun 27 '21 at 16:30
i just tried this but not getting the desired result – Naina Jun 27 '21 at 16:30
As you can see, I am - with sample data you posted. What I can tell - based on what you posted as a comment - is that ASOF looks *strange*. If it is supposed to be a DATE value then try with `and asof = date '2021-06-25'` – Littlefoot Jun 27 '21 at 19:56
it worked, many thanks for your time and effort, just one question like i have got 1000 rows with different entities do i need to create 1000 lines like the below select 'EEM.A', date '2021-06-25', 'J1530', 55.04, 55 from dual union all – Naina Jun 28 '21 at 06:06
You're welcome. You don't have to do anything like that; I don't have your tables nor data so I used a CTE to "create" several sample rows. Query you'd use begins at line #12, so you'd use just `select * from your_table pivot ...` – Littlefoot Jun 28 '21 at 06:07
i have got two tables one for entities ticker and another one for price – Naina Jun 28 '21 at 06:10

Need row data column wise in oracle

1 Answers1