Return string of SQL one to many values

Question

Hello,

I have a table: Task, and another TaskHistory

1 Task -> Many Task Histories

TaskHistory has a field named 'Comment'.

I would like to from SQL return a string of all of the Task History Comments for whatever TaskId I pass in.

Example: GetTaskHistory(@TaskId)

Returns: 'Comment: some comment \r\n Comment: another comment \r\n\ Comment: yet another'

I am wondering if returning this from SQL would be faster than a recordset that I loop through in my application to build a string.

Is this possible?

Thank you!

Answer 1

This is possible, however I would recommend keeping the formatting in the application and simple return the data from SQL. That is however my opinion on how applications should be separated.

SELECT Comment
FROM TaskHistory
WHERE TaskID = 1

To concatenate column results into a string you can do something like:

DECLARE @HistoryComments nvarchar(MAX) 

SELECT @HistoryComments = COALESCE(@HistoryComments + ' \r\n ', '') + Comment
FROM FROM TaskHistory
WHERE TaskID = 1

SELECT TaskHistorys = @HistoryComments