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

Question

I have a query with the following result: query:

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 to write my request in order to get the result as follows:

 TaskID Comment TimeSpent __________________________________ 111754 ,C1,C2,C3 84 

Thanks in advance.

Answer 1

Here's the working SQL script: http://sqlfiddle.com/#!3/3597a/3

This is where working SQL works.

 SELECT Tasks.TaskId, SUBSTRING( (SELECT ',' + Comments.Comment FROM Comments INNER JOIN tasks ON comments.entityid = tasks.taskid FOR XML PATH('')),2,200000) AS Comments , SUM(comments.timespent) AS TimeSpent FROM comments INNER JOIN tasks ON comments.entityid = tasks.taskid WHERE ( comments.entity = 1 ) GROUP BY Tasks.TaskId 

Create a table and fill in the data

 CREATE TABLE Tasks ( TaskID NVARCHAR(20) NOT NULL, ); CREATE TABLE Comments ( Entity INT NOT NULL, EntityID NVARCHAR(20) NOT NULL, Comment NVARCHAR(50) NOT NULL, TimeSpent INT NOT NULL ); INSERT INTO Tasks VALUES ( '111754' ); INSERT INTO Comments VALUES (1,'111754', 'C1',4 ), (1,'111754', 'C2',1 ), (1,'111754', 'C3',79 ); 

Run SQL

 SELECT Tasks.TaskId, SUBSTRING( (SELECT ',' + Comments.Comment FROM Comments INNER JOIN tasks ON comments.entityid = tasks.taskid FOR XML PATH('')),2,200000) AS Comments , SUM(comments.timespent) AS TimeSpent FROM comments INNER JOIN tasks ON comments.entityid = tasks.taskid WHERE comments.entity = 1 GROUP BY Tasks.TaskId 

View results.

 TASKID COMMENTS TIMESPENT 111754 C1,C2,C3 84 

Answer 2

You should look at FOR XML PATH .

Answer 3

You can use CROSS APPLY with an XML path, for example:

  ` Select * from table1 t1 CROSS APPLY ( SELECT [text()] = t.[Name] + '; ' FROM table2 t2 WHERE t1.[Id] = t2.[Id] ORDER BY t2.name FOR XML PATH('') ) a (Type) ` 

Answer 4

Well, this is a little more complicated, but it does not use xml, and it can be used with other databases than sql server:

  WITH orig AS (SELECT 1 AS f1, 'C11' AS f2 UNION ALL SELECT 1 AS f1, 'C12' AS f2 UNION ALL SELECT 1 AS f1, 'C13' AS f2 UNION ALL SELECT 2 AS f1, 'C21' AS f2 UNION ALL SELECT 2 AS f1, 'C22' AS f2 UNION ALL SELECT 2 AS f1, 'C23' AS f2 UNION ALL SELECT 3 AS f1, 'C31' AS f2) , orig2 AS (SELECT DISTINCT f1, f2 FROM orig) , orig3 AS (SELECT f1, f2, row_number() OVER(PARTITION BY f1 ORDER BY f2) AS RowNum FROM orig2) , orig4 -- Use recursion to concatenate the fields AS (SELECT f1, CONVERT(VARCHAR(MAX), f2) AS val, rownum FROM orig3 WHERE RowNum = 1 UNION ALL SELECT orig4.f1, orig4.val + ', ' + orig3.f2 AS val, orig3.rownum FROM orig4 INNER JOIN orig3 ON orig4.RowNum + 1 = orig3.RowNum AND orig4.f1 = orig3.f1) SELECT * FROM orig4 -- select only the rows that match the maximum rownum WHERE NOT EXISTS (SELECT 1 FROM orig4 o44 WHERE o44.f1 = orig4.f1 AND o44.rownum > orig4.rownum) 

Another approach that works only for the sql server would be to create an aggregated CLR function that combines the values: http://msdn.microsoft.com/en-us/library/91e6taax%28v=vs.90%29.aspx .

If you came across this article but are using an oracle, you have the option to use the above query or define a custom aggregation function in pl / sql ( http://docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions .htm ).