本文介绍了如何在一个查询中连接一列中的多个行+内连接的字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个查询,结果如下: 查询:
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结果: 任务ID评论TimeSpent _______ ________ _________ 111754 C1 4 111754 C2 1 111754 C3 79 请告诉我如何编写查询以获得结果低点: 任务ID评论TimeSpent _______ ___________ _________ 111754,C1,C2,C3 84 提前谢谢。
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.
推荐答案你需要使用一个SQL Server字符串连接技巧,如上所述此处。我个人更喜欢FOR XML PATH You need to use one of the SQL Server string concatenation tricks, as described here. I personally prefer FOR XML PATH 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
尝试 PIVOT [ ^ ]。
更多推荐
如何在一个查询中连接一列中的多个行+内连接的字符串
发布评论