Home » SQL & PL/SQL » SQL & PL/SQL » Select where show information from two row in one row
Select where show information from two row in one row [message #272931] Mon, 08 October 2007 04:22 Go to next message
antti
Messages: 2
Registered: October 2007
Location: finland
Junior Member
Hi
This is my first post here so please be kind!
My problem is that I need to show the result in one row but my query returns two
the result looks like this
|name |description |location |supplier |code |amount |
 xxx   something    p1        x123      EUR   2  
 xxx   something    p1        x123      JAK   6 X 50  
 yyy   something    p3        x123      EUR   2  
 eee   something    p6        x123      EUR   2  

as shown here there can be one or two rows per name and now I want this result looks like this
|name |description |location |supplier |code |amount |code2 |amount2 |
 xxx   something    p1        x123      EUR   2       JAK   6 X 50
 yyy   something    p3        x123      EUR   2
 eee   something    p6        x123      EUR   2

so if there is two returns for the same name they would show in same row else it be empty string
Re: Select where show information from two row in one row [message #272933 is a reply to message #272931] Mon, 08 October 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Please search before posting.
What you're trying to do is called "pivot" there are many examples of this, here and on other sites.
For instance, Joe Fuda's site: http://www.sqlsnippets.com/en/topic-11716.html, scroll down to "rows to column".

Regards
Michel
Re: Select where show information from two row in one row [message #272934 is a reply to message #272931] Mon, 08 October 2007 04:39 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The later link is not present now. File not found.
Re: Select where show information from two row in one row [message #272938 is a reply to message #272934] Mon, 08 October 2007 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, go to the site root page and click on SQL Techniques, then you are on this page.

Regards
Michel
Re: Select where show information from two row in one row [message #272939 is a reply to message #272931] Mon, 08 October 2007 04:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

[Edit from MC: direct link removed. Start from the root.]

[Updated on: Mon, 08 October 2007 05:41] by Moderator

Report message to a moderator

Re: Select where show information from two row in one row [message #273378 is a reply to message #272939] Wed, 10 October 2007 03:46 Go to previous message
antti
Messages: 2
Registered: October 2007
Location: finland
Junior Member
Thank you all for your help this is my solution for the problem.

select a.*, case 
                when a.box1_code like 'LTK%' then '1'
                when a.box1_code like 'PAHVI' then '1'
                when a.box1_code like 'JAKO2' then '1'
                when a.box1_code like 'PEU%' then '2'
                when a.box1_code like 'EU%' then '2'
                when a.box1_code like 'FI%' then '2'
                when a.box1_code like 'JAKO1' then '2'
            end box
from(
    select part, description, leadtime, location,
            lot_size, supplier_id, supplier,
            max(decode(pivot,1,loadcode,'')) box1_code,
            max(decode(pivot,1,amount,'')) box1_amount,
            max(decode(pivot,2,loadcode,'')) box2_code,
            max(decode(pivot,2,amount,'')) box2_amount
    from(
        select a.part_no part, a.Description description, 
            a.LEADTIME leadtime, b.LOCATION_NO location, 
            c.LOT_SIZE lot_size, d.VENDOR_NO supplier_id, 
            using_api.GET_Name(d.VENDOR_NO) supplier, 
            min(e.code) loadcode, e.value amount,
            rank() over(partition by a.part_no order by e.code) pivot
        from table1 a, table2 b, 
             table3 c, table4 d, 
             table5 e 
        where a.part_no = b.part_no and a.part_no = c.part_no 
        and a.part_no = d.part_no and a.part_no = e.part_no 
         and a.part_no in ('part1','part2') 
        group by a.part_no, a.Description, a.LEADTIME, b.LOCATION_NO, 
        c.LOT_SIZE, d.VENDOR_NO, e.code, e.value order by a.part_no     
    )
     ) a order by box, location


this is little bit modified but this is what I did. If some one can show easier way I'm happy to heard it.
And Thanks to all!!
Previous Topic: To fetch record using DECODE
Next Topic: find and delete records without parent key
Goto Forum:
  


Current Time: Fri Dec 09 11:46:19 CST 2016

Total time taken to generate the page: 0.16701 seconds