Results 1 to 2 of 2

Thread: A Query Doubt

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    A Query Doubt

    I am using the following two tables
    cmscaneweighment
    ryotno suppqty gross
    02098u002 54.665 49199
    02107d005 17.142 15428

    cmsloanissues
    ryotno loantype amount
    02098u002 manure loans 2500
    02098u002 advance 1500
    02107d005 manure loans 5000
    02107d005 seed loan 7500
    02107d005 advance 7000

    The output should be as follows
    ryotno suppqty gross manure avance seedloan
    02098u002 54.665 49199 2500 1500 0
    02107d005 17.142 15428 5000 7000 7500
    can we get the above output in a sigle query without writing a procedure?

    Last edited by krishnaindia2007; 12-31-2007 at 06:33 AM.

  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: A Query Doubt

    Select a
    , max(case column# when 1 then b end) -- first column
    -- if 2nd column not null, append after comma
    || case when
    max(case column# when 2 then b end)
    is not null then ','||
    max(case column# when 2 then b end)
    end
    -- if 3rd column not null, append after comma
    || case when
    max(case column# when 3 then b end)
    is not null then ','|| max(case column# when 3 then b end)
    end as resulting_csv
    from ( select a.ryotno ||' '||a.suppqty ||' '||a.gross a,b.amount b, row_number() over
    (partition by a.ryotno
    order by a.ryotno ) column#
    from cmscaneweighment a,cmsloanissues b where a.ryotno=b.ryotno )
    group by a
    order by a

    Try this

    Last edited by susarlasireesha; 12-31-2007 at 07:45 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact