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.

 

 

 

 

Silverlight Non-Descript Errors When Deploying Using WCF Services

You deliver a Silverlight module that uses WCF Services to a production server.  It works great on your machine, but when you run it in production, you get an error every time you call out to a WCF service.  

 

The issue may be in the ServiceReference.Config file.  In the <System.serviceModel> section, in the <client> section, you will see endpoints for every service.  Change the 'address' of these to the endpoints that you use in production, recompile, and deliver the module.