Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2015
    Posts
    4

    Answered: SQL Complex Query help

    I have a table with the following Data.

    AccountNumber ApplicantId ApplicantName
    100 0 John
    100 1 Jill
    101 0 Josh
    102 0 Jack
    102 1 Jarred
    102 2 Johana

    Need output in the following format. What SQL query I can use in DB2. Thanks

    AccountNumber Applicant0_Name Applicant0 Applicant1 Applicant2
    100 John Y Y N
    101 Josh Y N N
    102 Jack Y Y Y

  2. Best Answer
    Posted by mark.b

    "Hi,

    try this:
    Code:
    with tab (AccountNumber, ApplicantId, ApplicantName) as (values
      (100, 0 ,'John')
    , (100, 1 ,'Jill')
    , (101, 0 ,'Josh')
    , (102, 0 ,'Jack')
    , (102, 1 ,'Jarred')
    , (102, 2 ,'Johana')
    )
    select AccountNumber, ApplicantName
    --, 'Y' Applicant0
    , case when cnt_ > 1 then 'Y' else 'N' end  Applicant1 
    , case when cnt_ > 2 then 'Y' else 'N' end  Applicant2
    from (
    select t.*
    , rownumber() over (partition by AccountNumber order by applicantid) rn_
    , count(1) over (partition by AccountNumber) cnt_
    from tab t
    )
    where rn_=1
    
     ACCOUNTNUMBER APPLICANTNAME APPLICANT1 APPLICANT2
     ------------- ------------- ---------- ----------
               100 John          Y          N
               101 Josh          N          N
               102 Jack          Y          Y
    "


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without a description of what rules determine the values, the only correct answer I can provide would be:
    Code:
    SELECT AccountNumber, Applicant0_Name, Applicant0
    ,  Applicant1, Applicant2
       FROM (VALUES
       (100, 'John', 'Y', 'Y', 'N')
    ,  (101, 'Josh', 'Y', 'N', 'N')
    ,  (102, 'Jack', 'Y', 'Y', 'Y')) AS v (
       AccountNumber, Applicant0_Name, Applicant0
    ,  Applicant1, Applicant2)
    If you can describe your requirements better, I may be able to provide a better solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Aug 2015
    Posts
    4

    Clarification

    Thanks for the reply, here is my requirement.

    Account# person# personname
    100 1 John
    100 2 Jill

    101 1 Josh

    102 1 Jack
    102 2 Jarred
    102 3 Johana


    each account can have applicant 1, 2, 3 people on the account (there is always 1 person, both 2, 3 are optional).
    in the out put I need to list only applicant 1, but indicate a flag if there is applicant 1 or 2 present on this account.
    ( in the above example account 100 has 2 applicants, 101 has just 1, 102 has 3)

    account number # applicant1 name #, applicant2 present#(Y/N) applicant3 present# (Y/N)

    100 John Y N
    101 Josh N N
    102 Jack Y Y

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keeping in mind that there is no order unless you can specify one, do you pick the order at random or some other way? In other words, does something determine the order of the rows or are they just random?

    I notice that you have multiple "person 1", etc. Does that indicate the order for a given account???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Aug 2015
    Posts
    4

    order

    The order is by the account #


    Account# person# personname
    100 1 John
    100 2 Jill

    101 1 Josh

    for example account number 100 has 2 people, account number 101 has just one person.
    So the output is needed like this.

    100 Y Y
    101 Y N

    Thanks.

  7. #6
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I don't think your table structure is normalized adequately. Is this a real life database or just a play area?
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  8. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    query is not complex. KISS(Keep It Simple, Stupid) always use this in life. Also, keep in mind this is just quick off top of head and you will need to correct it to fit your assignment.

    Code:
    select account, applicant_0, name
    ,value(select 'Y' from acct b
              where a.acct = b.acct
                  and person > 1)
    ,value(select 'Y' from acct b
              where a.acct = b.acct
                  and person > 2)
    etc..., etc...,etc...
    or maybe to be more efficient you use a case statement around the above and instead of selecting Y, you use a count * then depending on that count you give back the appropriate Y/N responses.


    Keep in mind as was already told your table is not properly normalized.
    Dave

  9. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    with tab (AccountNumber, ApplicantId, ApplicantName) as (values
      (100, 0 ,'John')
    , (100, 1 ,'Jill')
    , (101, 0 ,'Josh')
    , (102, 0 ,'Jack')
    , (102, 1 ,'Jarred')
    , (102, 2 ,'Johana')
    )
    select AccountNumber, ApplicantName
    --, 'Y' Applicant0
    , case when cnt_ > 1 then 'Y' else 'N' end  Applicant1 
    , case when cnt_ > 2 then 'Y' else 'N' end  Applicant2
    from (
    select t.*
    , rownumber() over (partition by AccountNumber order by applicantid) rn_
    , count(1) over (partition by AccountNumber) cnt_
    from tab t
    )
    where rn_=1
    
     ACCOUNTNUMBER APPLICANTNAME APPLICANT1 APPLICANT2
     ------------- ------------- ---------- ----------
               100 John          Y          N
               101 Josh          N          N
               102 Jack          Y          Y
    Regards,
    Mark.

  10. #9
    Join Date
    Aug 2015
    Posts
    4

    Thanks

    Thank you, that logic worked out perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •