SQL Server Query – Show Primary and Secondary Accounts

querysql server

I have a table that provides the relationships between Primary and Secondary accounts which looks like this:

CREATE TABLE testRelate
(
  AccountRef nvarchar(20) PRIMARY KEY,
  RelationshipType nvarchar(20),
  RelationshipRef nvarchar(10),
  PrimaryRelationshipRef nvarchar(10)
);

data

I am trying (with no success) to find a way to show each account and its related accounts in a single row. Something like this:

output

The format is not terribly important (All the Primary/Secondary Account numbers could be concatenated into one field if it makes it easier, though if the first could be Primary that would be preferable). As long as each account shows all its related accounts in one row I would be more than happy! In the data the maximum number of secondary accounts so far is 4, but that is not fixed.

The best I have managed is

SELECT
    testRelate.AccountRef AS ACref, 
    testRelate_2.AccountRef
FROM
    testRelate AS testRelate_2 
INNER JOIN
    testRelate AS testRelate_1 
    ON testRelate_2.[RelationshipRef] = testRelate_1.[RelationshipRef] 
RIGHT OUTER JOIN 
    testRelate 
    ON testRelate_1.[PrimaryRelationshipRef] = testRelate.[PrimaryRelationshipRef];

Which produces the desired matches but not in one line.

Best Answer

If you use sql server 2017 you could use the STRING_AGG function and your query would look like

SELECT testRelate.AccountRef AS ACref,STRING_AGG(testRelate_2.AccountRef, ', ')
FROM testRelate AS testRelate_2 
INNER JOIN  testRelate AS testRelate_1 ON testRelate_2.[Relationship Reference] = testRelate_1.[Relationship Reference] 
RIGHT OUTER JOIN testRelate ON testRelate_1.[Primary Relationship Reference] = testRelate.[Primary Relationship Reference]
GROUP BY testRelate.AccountRef

but if not, it would have to be with FOR XML PATH Y STUFF