Combine results from sql query

Question

Hi,

I have three tables in sql, CUSTOMER, ISSUE, ISSUE_NOTES.

SELECT CUSTOMER.name, ISSUE.description, ISSUE_NOTES.notes
FROM  CUSTOMER, ISSUE, ISSUE_NOTES
WHERE CUSTOMER.customer_id = ISSUE.customer_id
AND ISSUE_NOTES.incident_id = ISSUE_NOTES.incident_id

This will produce a row for each issue_notes field that's populated. (field is VARCHAR2(4000)

I want to concatenate the notes field(s) (from the issue_notes table) into one field. (I'm archiving information into another database - this statement will be ran weekly)

The aim being I will have one row with a large notes field being the sum of all text from the above syntax, is this possible?

Thanks.

Answer 1

You can use COALESCE:

Here's a taste for it:

DECLARE @output varchar(1000)
(SELECT @output = COALESCE(@output + ', ', '') + notes FROM ISSUE_NOTES)
SELECT @output