Forum Stats

  • 3,728,425 Users
  • 2,245,615 Discussions
  • 7,853,521 Comments

Discussions

select result without same rows

Hi!

Could you please give me advice, i have some select

select 

o.field1,

o.field2, 

o.field3, 

b.field4, 

b.field5

from orders o 

inner join bill b

on o.orders_id = b.orders_id 

and b.PARAM_ID in ('PARAM1', 'PARAM2', 'PARAM3');


and result set will be something like this, i need to get fields 3-4-5, but field 1-2 will be the same;

field1 field2 field3 (PARAM1)

field1 field2 field4 (PARAM2)

field1 field2 field5 (PARAM3)

...

field1 field2 field3 (PARAM1)

field1 field2 field4 (PARAM2)

field1 field2 field5 (PARAM3)


how i can change it, to get data like this, without repeating field1 field2 and selecting their field 3-4-5 at row :

field1 field2 PARAM1 PARAM2 PARAM3

...

field1 field2 PARAM1 PARAM2 PARAM3

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,319 Red Diamond
    edited December 2020

    Hi,

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

    See: How to Ask Questions in Developer Community Spaces - oracle-tech


    Could you please give me advice, i have some select

    select 

    o.field1,

    o.field2, 

    o.field3, 

    b.field4, 

    b.field5

    from orders o 

    inner join bill b

    on o.orders_id = b.orders_id 

    and b.PARAM_ID in ('PARAM1', 'PARAM2', 'PARAM3');


    and result set will be something like this, i need to get fields 3-4-5, but field 1-2 will be the same;

    field1 field2 field3 (PARAM1)

    field1 field2 field4 (PARAM2)

    field1 field2 field5 (PARAM3)

    ...

    You must have copied something wrong. The result set of the query you posted will NOT be like the results you posted. The query will produce 5 columns, not 3. (Or is it 4?)

    If you don have a query that produces 3 rows, and you change the result set to contain the same values in 3 columns on te same row, that's called Pivoting. Exactly how you do it depends on your data, your requirements and your verfsion.

    BEDE
Sign In or Register to comment.