Post reply

Tricky Query - Help Please?

  • May 10, 2007 at 8:19 am

    #120006

    Ok I have two tables as below.

    CREATE TABLE Case (

    [CaseID] [int] IDENTITY (1, 1) NOT NULL --PRIMARY KEY

    )

    CREATE TABLE Borrower (

    [BorrowerID] [int] IDENTITY (1, 1) NOT NULL, --PRIMARY KEY

    [CaseID] [int] --Foreign Key

    )

    There can between 1 to 4 borrowers per case.

    Each case has at least 1 borrower.

    I want to be able to write a select query for reporting purposes that will output something similar to the following;

    CaseID     BorrowerID

    14524      785

    14875      786

    14875      787

    14875      788

    14865      850

    I have thought about using min and max on the borrowerID however this isn't the best option as it won't be able to pick up cases that have more than 2 borrowers! This is a real problem I come up against often in my workplace, a simple efficient solution would be great and highly appreciated!

  • May 10, 2007 at 8:23 am

    #705507

    Can you provide sample data as it looks in each of the tables? That way we can compare the original data to the results that you want.

    -SQLBill

  • May 10, 2007 at 8:32 am

    #705517

    Try this:

    select

        Case.CaseID,

        Borrower.BorrowerID

    from

        dbo.Case

        inner join dbo.Borrower on (Case.CaseID = Borrower.CaseID)

     

  • May 10, 2007 at 8:33 am

    #705518

    There are many other columns in these tables but I am using the columns to be joined to keep things much simpler.

    The sample data looks something like this;

    Case Table

    CaseID
    1589
    1590
    1591
    1592
    1593

    Borrower Table

    BorrowerIDCaseID
    77501589
    77511590
    77521590
    77531590
    77541590
    77551591
    77561591
    77571592
    77581593
    77591593
    77601593

    I look forward to you replies!

  • May 10, 2007 at 9:09 am

    #705533

    See post above yours.....  I just Editted it. 

  • May 10, 2007 at 9:22 am

    #705539

    Hmm, that seems too easy. Let me check I got the problem exactly right...

  • May 10, 2007 at 9:39 am

    #705544

    CaseID                   BorrowerID1              BorrowerID2            BorrowerID3            BorrowerID4

    1589                      7750

    1590                      7751                        7752                      7753                      7754

     

    Sorry I got it wrong! I require the output to run like this horizontally. Apologies.

  • May 10, 2007 at 1:13 pm

    #705633

    I believe you can do something to this effect using that same query, but adding a PIVOT.

    I don't have the time to get the query working, but you can find a small example article here http://sqlserver2000.databases.aspfaq.com/how-do-i-create-a-cross-tab-or-pivot-query.html

  • May 10, 2007 at 2:10 pm

    #705657

    You need SQL 2005 for this, but the following accomplishes what you want:

    --This just create a table like your "Borrower" table (only in memory)

    declare @borrower_ table (caseid_ int, borrowerid_ int)

    --Populate my "borrower" table

    insert into @borrower_ values (1589,7750)

    insert into @borrower_ values (1590,7751)

    insert into @borrower_ values (1590,7752)

    insert into @borrower_ values (1590,7753)

    insert into @borrower_ values (1590,7754)

    insert into @borrower_ values (1591,7755)

    insert into @borrower_ values (1591,7756);

    insert into @borrower_ values (1592,7757);

    insert into @borrower_ values (1593,7758);

    insert into @borrower_ values (1593,7759);

    insert into @borrower_ values (1593,7760);

    --This is a Common Table expression

    -- you could eliminate this and simply place the SELECT statement in the FROM clause

    -- of the main select statement (but I think this helps with readability once you get use to it

    with mycte as

    (

     select caseid_, borrowerid_, row_number() over (partition by caseid_ order by caseid_) as rownum_ from @borrower_

    )

    select caseid_, [1] as borrower1, [2] as borrower2, [3] as borrower3, [4] as borrower4, [5] as borrower5

       from mycte

       pivot

       (

           max(borrowerid_)

           for rownum_

           in ([1],[2],[3],[4],[5])

       ) as p

    order by caseid_

     

    /* -- OUTPUT:

    caseid_     borrower1   borrower2   borrower3   borrower4   borrower5

    ----------- ----------- ----------- ----------- ----------- -----------

    1589        7750        NULL        NULL        NULL        NULL

    1590        7751        7752        7753        7754        NULL

    1591        7755        7756        NULL        NULL        NULL

    1592        7757        NULL        NULL        NULL        NULL

    1593        7758        7759        7760        NULL        NULL

    */

     

  • May 11, 2007 at 4:21 am

    #705758

    Thanks for your help guys, I rarely use pivots but I am experimenting now. The problem is I am running SQL Server 2000 which doesn't have the OVER or Row_Number functions - am I right in saying this?

  • May 11, 2007 at 4:26 am

    #705761

    I can't even use PIVOT either can I?!

  • May 11, 2007 at 6:09 am

    #705778

    Yes you are correct SQL 2000 does not have those capabilities.  Sorry about that, I didn't pay close enough attention to the category before answering.

    James.

  • May 21, 2007 at 10:06 am

    #707868

    This is expensive, and depending on how much data you have there are better solutions...

    SELECT
    CaseID,
    MAX(Borrower_1) AS Borrower_1,
    MAX(Borrower_2) AS Borrower_2,
    MAX(Borrower_3) AS Borrower_3,
    MAX(Borrower_4) AS Borrower_4
FROM (
    SELECT
        CaseID,
        CASE Rank WHEN 1 THEN BorrowerID ELSE 0 END AS Borrower_1,
        CASE Rank WHEN 2 THEN BorrowerID ELSE 0 END AS Borrower_2,
        CASE Rank WHEN 3 THEN BorrowerID ELSE 0 END AS Borrower_3,
        CASE Rank WHEN 4 THEN BorrowerID ELSE 0 END AS Borrower_4
    FROM (
        SELECT 
            b1.CaseID, 
            b1.BorrowerID, 
            (
                SELECT COUNT(*) 
                FROM dbo.Borrower AS b2 
                WHERE b2.CaseID = b1.CaseID 
                AND b1.BorrowerID <= b2.BorrowerID
            ) AS Rank
        FROM 
            dbo.Borrower AS b1
        ) AS ranked
    ) AS pivoted
GROUP BY
    CaseID
ORDER BY
    CaseID ASC

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply