T-SQL Concat 2 Rows with same Id from Join

Question

I'm currently working on a query in T-SQL on SQL Server 2012. I gather several Titles from a table via a join from another table. My tables look like this:

Table Project:

 ProjectId |
-----------+
1          |   
2          |   
3          |

Table ProjectResources

ResourceId |    Label      | ProjectId
-----------+---------------+-----------
2          |   Resource X  |  1 
3          |   Resource Y  |  1
4          |   Resource Z  |  2
5          |   Resource A  |  2
6          |   Resource X  |  3

the desired output in my temporary table should look like this:

ProjectId  |   Label                  |
-----------+--------------------------+
1          |   Resource X, Resource Y |
2          |   Resource Z, Resource A |
3          |   Resource X             |

I want to concat all Labels with the same ProjectId. My current query looks like this:

CREATE TABLE #tmpProjects
(
    ProjectId INT
    , Label VARCHAR(1000)
)

SELECT [P].ProjectId, [PR].Label 
    INTO #tmpProjects
FROM [MySchema].[ProjectResources] [PR]
INNER JOIN dbo.Project [P] ON [P].ProjectId= [PR].ProjectId

Unfortunatly my query creates a new row for each Resouce Label. Do you know how to create a new temp table with "concatinated" merged rows for each resource holding the same ProjectId?

Thanks a lot!

You are asking how to concatenate columns, not rows. There are various techniques, described and benchmarked in [Grouped Concatenation in SQL Server](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation) by Aaron Bertrand. The fastest technique uses SQLCLR, the second fastest XML. `STRING_AGG` will be supported natively in the next version of SQL Server, SQL Server 2017 — Panagiotis Kanavos, Apr 24 '17 at 09:27

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

You could use For xml path, stuff like this

SELECT p.ProjectId,
     STUFF((     SELECT ', ' + pr.Label 
            FROM ProjectResources pr 
            WHERE  pr.ProjectId = p.ProjectId FOR XML PATH('')
           )
         ,1,2,'') as Label
FROM Project p

edited May 23 '17 at 12:02

Community

1
1

answered Apr 24 '17 at 09:20

TriV

5,118
2
10
18

Thanks for your help and the good hint, I did some research and apparently For Xml path is a very fast solution for this common problem. :) – TimHorton Apr 24 '17 at 10:00
You are welcome ... – TriV Apr 24 '17 at 10:04

score 2 · Answer 2 · answered Apr 24 '17 at 09:22

CREATE TABLE #Table2
    ([ResourceId] int, [Label] varchar(10), [ProjectId] int)
INSERT INTO #Table2
    ([ResourceId], [Label], [ProjectId])
VALUES
    (2, 'Resource X', 1),
    (3, 'Resource Y', 1),
    (4, 'Resource Z', 2),
    (5, 'Resource A', 2),
    (6, 'Resource X', 3)
SELECT p.ProjectId,
     STUFF((     SELECT ', ' + pr.Label 
            FROM (select a.ProjectId,ResourceId,Label from #Table1 A join #Table2 B on a.ProjectId=b.ProjectId) pr 
            WHERE  pr.ProjectId = p.ProjectId FOR XML PATH('')
           )
         ,1,2,'') as Label
FROM (select a.ProjectId,ResourceId,Label from #Table1 A join #Table2 B on a.ProjectId=b.ProjectId) p
group by  p.ProjectId

output 

ProjectId  |   Label                  |
-----------+--------------------------+
1          |   Resource X, Resource Y |
2          |   Resource Z, Resource A |
3          |   Resource X             |

A code-only answer isn't considered a good answer. Please explain *why* this code answers the question. This is just one of the techniques available to aggregate strings and it's not even the fastest one — Panagiotis Kanavos, Apr 24 '17 at 09:23

T-SQL Concat 2 Rows with same Id from Join

2 Answers2