Wednesday, February 24, 2010

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.
 
 
 
 

No comments:

Post a Comment