1

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.

3

2 Answers 2

1

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

1

I'm upvoting Edgar's answer because he posted first, but here is a more fleshed out example using either STRING_AGG or the FOR XML trick:

CREATE TABLE #AccountLinks
(
    AccountRef              VARCHAR(10),
    RelationshipType        VARCHAR(10),
    RelationshipRef         VARCHAR(10),
    PrimaryRelationshipRef  VARCHAR(10)
)

INSERT INTO #AccountLinks
(
    AccountRef,
    RelationshipType,
    RelationshipRef,
    PrimaryRelationshipRef
)
VALUES
('2099134', 'PRIMARY', 'P_00034', 'P_00034'),
('2113256', 'PRIMARY', 'P_00044', 'P_00044'),
('2099143', 'SECONDARY', 'S_00034', 'P_00034'),
('2099152', 'SECONDARY', 'S_00035', 'P_00034'),
('2099161', 'SECONDARY', 'S_00036', 'P_00034'),
('1449346', 'SECONDARY', 'S_00049', 'P_00044'),
('0731048', 'SECONDARY', 'S_00047', 'P_00044'),
('1402792', 'SECONDARY', 'S_00048', 'P_00044'),
('8858908', 'PRIMARY', 'P_00037', 'P_00037'),
('8858917', 'SECONDARY', 'S_00039', 'P_00037'),
('8858926', 'SECONDARY', 'S_00040', 'P_00037'),
('0935714', 'PRIMARY', 'P_00510', 'P_00510'),
('0935723', 'SECONDARY', 'S_00713', 'P_00510'),
('0935741', 'SECONDARY', 'S_00715', 'P_00510'),
('1242998', 'PRIMARY', 'P_00455', 'P_00455'),
('1243004', 'SECONDARY', 'S_00715', 'P_00455'),
('8048567', 'PRIMARY', 'P_00331', 'P_00331'),
('8048576', 'SECONDARY', 'S_00448', 'P_00331'),
('9999999', 'PRIMARY', 'P_99999', 'P_99999')

-- Only Works on SQL 2017+
SELECT  al_p.AccountRef AS [PRIMARY], STRING_AGG(al_c.AccountRef, ', ') AS SECONDARIES
FROM    #AccountLinks al_p
            LEFT JOIN #AccountLinks al_c
                ON al_p.RelationshipRef = al_c.PrimaryRelationshipRef
                AND al_c.RelationshipType = 'SECONDARY'
WHERE   al_p.RelationshipType = 'PRIMARY'
GROUP BY al_p.AccountRef

-- One approach for older SQL Server versions using FOR XML
SELECT  al_p.AccountRef AS [PRIMARY]
     , STUFF((SELECT    ', ' + al_c.AccountRef
        FROM    #AccountLinks al_c
        WHERE   al_c.PrimaryRelationshipRef = al_p.RelationshipRef
            AND al_c.RelationshipType = 'SECONDARY'
        FOR XML PATH('')), 1, 2, '') AS SECONDARIES
FROM    #AccountLinks al_p
WHERE   al_p.RelationshipType = 'PRIMARY'

dbfiddle.uk

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.