Combining multiple rows into one row.

Posted on 2006-05-17
Last Modified: 2008-03-17
I have to combine data from three tables where more than one table has multiple rows into just one row and I have an instance where one relationship has no clear hiearchy. See example of the data below.

AccountNo | AccountInfo | AccountStatus | AccountType
     1A               XYZ                Active                6

AccountNo | AccountCustomer | AccountRelationship
    1A                  123                         Primary
    1A                  345                         Secondary
    1A                  678                         Secondary

AccountCustomer | AccountCustomerName
     123                           Mr. X
     345                           Ms. Y
     678                           Ms. X

I need to produce the table below from these 3 tables above.

   1A                123                  Mr. X                       345                        Ms. Y                            678
           Ms. X


Question by:Sue_W
    LVL 34

    Expert Comment

    by:Brian Crowe
    Is there a limit on the number of customer accounts per account?

    Author Comment

    Up to five account relationships can be associated with a primary account but I only have room for 2 of the possible 5 in my row.

    So account: 1A can have a Primary Account - like 123 plus 345 as a Secondary and 456 as a Secondary and 678 as a Secondary and 789 as a Secondary and 987 as a Secondary. The only difference between the accounts is the AccountCustomer ID. These are all in individual rows in TableTwo and TableThree.
    LVL 34

    Expert Comment

    by:Brian Crowe
    If there were different relationship definitions for the secondaries (secondary1, secondary2, etc) this would be much easier.  What precedence do you want to give for secondary relationships?  Do you just want to pull the first two in order of AccountCustomer or AccountCustomerName?

    Author Comment

    Yes - the relationship definition being the same is my primary issue. I had created a select that returned the row easily as long as I was dealing with hiearchical relationships but once I lost that I couldn't get the relationships to populate customer2 and customer3 correctly.

    LVL 34

    Expert Comment

    by:Brian Crowe
    Below is a function that you can use to return the nth AccountCustomer for a given AccountNo.

    CREATE FUNCTION dbo.udf_GetSecondaryAccountCustomer(@AccountNo char(10), @Rank tinyint)
    RETURNS int

    DECLARE @AccountCustomer int

    SELECT IDENTITY(int, 1, 1) AS Rank, AccountNo, AccountCustomer
    INTO #TableTwo
    FROM TableTwo
    WHERE AccountNo = @AccountNo
       AND AccountRelationship = 'Secondary'
    ORDER BY AccountCustomer

    SELECT @AccountCustomer = AccountCustomer
    FROM #TableTwo
    WHERE Rank = @Rank

    RETURN @AccountCustomer

    SELECT TableOne.AccountNo,
       dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 1) AS 1stSecondaryAccountCustomer,
       dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 2) AS 2ndSecondaryAccountCustomer
    FROM TableOne
    LEFT OUTER JOIN TableTwo AS PrimRel
       INNER JOIN TableThree AS PrimAcct
          ON PrimRel.AccountCustomer = PrimAcct.AccountCustomer
       ON TableOne.AccountNo = PrimRel.AccountNo
       AND PrimRel.AccountRelationship = 'Primary'
    LVL 34

    Accepted Solution

    SELECT AccountCustomers.AccountNo,
       Prim.AccountCustomerName AS PrimaryAcctName,
       Sec1.AccountCustomerName AS 1stSecondaryAccctName,
       Sec2.AccountCustomerName AS 2ndSecondaryAcctName
    FROM (
       SELECT TableOne.AccountNo,
          PrimRel.AccountCustomer AS PrimaryAccountCustomer,
          dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 1) AS 1stSecondaryAccountCustomer,
          dbo.udf_GetSecondaryAccountCustomer(TableOne.AccountNo, 2) AS 2ndSecondaryAccountCustomer
       FROM TableOne
       LEFT OUTER JOIN TableTwo AS PrimRel
          ON TableOne.AccountNo = PrimRel.AccountNo
          AND PrimRel.AccountRelationship = 'Primary'
       ) AS AccountCustomers
    LEFT OUTER JOIN TableThree AS Prim
       ON AccountCustomers.PrimaryAccountCustomer = Prim.AccountCustomer
    LEFT OUTER JOIN TableThree AS Sec1
       ON AccountCustomers.1stSecondaryAccountCustomer = Sec1.AccountCustomer
    LEFT OUTER JOIN TableThree AS Sec2
       ON AccountCustomers.2ndSecondaryAccountCustomer = Sec2.AccountCustomer

    Author Comment

    That works. I can't use a function but it gives me some ideas to try.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now