sql-server – 如何在一个查询中连接一列内连接中多行的字符串

我有一个查询结果如下：
查询：

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.

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

（编辑：佛山站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!