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 should I write my query to get the result as follows:
TaskID Comment TimeSpent
__________________________________
111754 ,C1,C2,C3 84
Thanks in advance.
Here's the working SQL Fiddle: http://sqlfiddle.com/#!3/3597a/3
Here's the actual working 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,'111754', 'C2',1 ),
(1,'111754', 'C3',79 );
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
TASKID COMMENTS TIMESPENT
111754 C1,C2,C3 84
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With