Craig Craig - 7 months ago 11
SQL Question

Multiple rows into one line, when there's a group

I need to get a list of all Activities in our system. An Activity can have 1:many service providers who manage the activity - so I have an Ativity table, with a many to many linking table (ActivityServiceProvider) and a list of Service providers.

A query to get the data looks like this.

SELECT a.id, sp.Description
FROM Activity a
INNER JOIN dbo.ActivityServiceProvider asp
ON asp.ActivityID = a.ID
INNER JOIN dbo.ServiceProvider sp
ON sp.ID = asp.ServiceProviderID


But as there might be more than one service provider per activity, I need to somehow use STUFF to get the list of Service Providers into a single row column.

I tried this, but it's failing with jsut a single row being returned:

SELECT
STUFF((
SELECT a.ID, ', ' + MAX(sp.Description)
FROM Activity a
INNER JOIN dbo.ActivityServiceProvider asp
ON asp.ActivityID = a.ID
INNER JOIN dbo.ServiceProvider sp
ON sp.ID = asp.ServiceProviderID
GROUP BY a.ID
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues


Can anyone spot the issue? The 'STUFF' and FOR XML keyword confuses me, so I might be making a basic error, but I am trying to return an ActivityID, and a ServiceProviders column, with a comma separated list of Service Providers.

Answer

You need an outer query to fetch each activity. Then the subquery combines the results for each description. There is no need for aggregation in the subquery -- the FOR XML PATH() takes care of that:

SELECT a.*,
       STUFF((SELECT ', ' + sp.Description
              FROM dbo.ActivityServiceProvider asp INNER JOIN
                   dbo.ServiceProvider sp
                   ON sp.ID = asp.ServiceProviderID
              WHERE asp.ActivityID = a.ID
              FOR XML PATH(''), TYPE
             ).value('.', 'VARCHAR(MAX)'
                    ), 1, 2, ''
            ) AS NameValues
FROM activity a;