我有一个查询结果如下：
查询：
查询：
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.timespent
结果：
TaskID Comment TimeSpent __________________________ 111754 C1 4 111754 C2 1 111754 C3 79
请告诉我如何编写查询以获得如下结果：
TaskID Comment TimeSpent __________________________________ 111754,C1,C2,C3 84
提前致谢.
解决方法
这是工作的sql小提琴：
http://sqlfiddle.com/#!3/3597a/3
这是实际工作的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
创建表并填充数据
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,'C2',1 ),'C3',79 );
执行sql
SELECT Tasks.TaskId,SUM(comments.timespent) AS TimeSpent FROM comments INNER JOIN tasks ON comments.entityid = tasks.taskid WHERE comments.entity = 1 GROUP BY Tasks.TaskId
查看结果.
TASKID COMMENTS TIMESPENT 111754 C1,C3 84
