How to concatenate strings from multiple rows in one column + inner join in one query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have a query with the following result:
query:

SQL

SELECT Tasks.TaskId, Comments.Comment, comments.timespent       
          FROM   comments
          INNER JOIN tasks ON comments.entityid = tasks.taskid                
          WHERE  ( comments.entity = 1 ) 
          GROUP  BY Tasks.TaskId, Comments.Comment, comments.timespent

result:

TaskID Comment TimeSpent
_______ ________ _________
111754 C1 4
111754 C2 1
111754 C3 79

Please tell me how should I write my query to get the result as follows:

TaskID Comment TimeSpent
_______ ___________ _________
111754 ,C1,C2,C3 84

Thanks in advance.

Posted 27-Jan-14 6:51am

saleem_deek

Add a Solution

2 solutions

Solution 1

Try PIVOT[^].

Posted 27-Jan-14 7:03am

Abhinav S

Comments

chaau 27-Jan-14 23:09pm

I think PIVOT will not do what the OP asks. Do you want to review your answer?

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Accepted Answer · 2014-01-27T17:23:00

You need to use one of the SQL Server string concatenation tricks, as described here. I personally prefer FOR XML PATH

SQL

SELECT Tasks.TaskId, SUM(comments.timespent),
stuff( (SELECT ','+Comment
               FROM comments p2
               WHERE p2.entityid = comments.entityid
               ORDER BY Comment
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
            ,1,1,'')
       AS Comments
          FROM   comments
          INNER JOIN tasks ON comments.entityid = tasks.taskid                
          WHERE  ( comments.entity = 1 ) 
          GROUP  BY Tasks.TaskId

How to concatenate strings from multiple rows in one column + inner join in one query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How to concatenate strings from multiple rows in one column + inner join in one query

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us