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)
);
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:
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
but if not, it would have to be with FOR XML PATH Y STUFF