Concatenating Strings Across Records In SQL Server

Occasionally it is necessary to concatenate strings from a number of different records and return them as a single value in another query.   For example, in the classic invoice/detail scenario, maybe you want a list of invoices, and with each invoice, a comma delimited list of all the descriptions of each detail record.  But this detail information has to be returned as a single text field as part of the invoice.  

 

To do this you need to create a function:

 

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'DetailList')
BEGIN
  DROP  FUNCTION DetailList
END
GO

CREATE FUNCTION DetailList@InvoiceId INT) RETURNS varchar(max)
BEGIN

DECLARE @Build varchar(max)

SELECT @Build=''

SELECT @Build = @Build + Description +  ', '

  FROM Detail

WHERE Detail.InvoiceId = @InvoiceId

RETURN @Build

END
GO

 

 

When calling this function you return it as if it were a field in the invoice table.   Don't forget to prefix it with "dbo."

select Amount, dbo.