Combining multiple rows into one row.

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.

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

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

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


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

TableFour
AccountNo|PrimaryCustNo|PrimaryAcctName|1stSecondaryCustNo|1stSecondaryAcctName|2ndtSecondaryCustNo  
   1A                123                  Mr. X                       345                        Ms. Y                            678
|2ndSecondaryAcctName
           Ms. X

Thanks!




Sue_WAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
SELECT AccountCustomers.AccountNo,
   AccountCustomers.PrimaryAccountCustomer,
   Prim.AccountCustomerName AS PrimaryAcctName,
   AccountCustomers.1stSecondaryAccountCustomer,
   Sec1.AccountCustomerName AS 1stSecondaryAccctName,
   AccountCustomers.2ndSecondaryAccountCustomer,
   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
0
 
Brian CroweDatabase AdministratorCommented:
Is there a limit on the number of customer accounts per account?
0
 
Sue_WAuthor Commented:
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.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Brian CroweDatabase AdministratorCommented:
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?
0
 
Sue_WAuthor Commented:
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.

0
 
Brian CroweDatabase AdministratorCommented:
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
BEGIN

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
END

SELECT TableOne.AccountNo,
   PrimRel.AccountCustomer,
   PrimAcct.AccountCustomerName,
   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'
0
 
Sue_WAuthor Commented:
That works. I can't use a function but it gives me some ideas to try.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.