Transform a table column into a CSV field in SQL Server

Imagine that you want to use a related table in your SQL query. You then must return only a single column or otherwise you'll get this SQL error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What I like to do, is to transform the result table column into a single field separated by a ; sign. Earlier on I used to create a huge function which used cursors etc. Then a colleague of mine told me how to use C# code in SQL server which seemed to be a neater solution.

Today I have found a new approach. This uses a function again, but it is very simple. Here's the function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Ronald Lemmen
-- Create date: 4 July 2008
-- Description: Function to return the people in CSV format belonging to a activity based on the activity party type code
-- =============================================
CREATE FUNCTION fn_RL_GetActivityPartyList
(
@ActivityId uniqueidentifier,
@ActivityPartyType int
)
RETURNS nvarchar(3000)
AS
BEGIN
DECLARE @result nvarchar(3000)

SET @result = ''

SELECT @result = Coalesce(@result + ';', '') + partyidname from filteredactivityparty party where party.activityid = @ActivityId and party.participationtypemask = @ActivityPartyType

RETURN SUBSTRING(@result ,2, LEN(@result))
END
GO

The @result will get filled by the SELECT query with the values from the filteredactivityparty table separated by a ;. The first character is a ; as well and therefore I do remove this one in the RETURN statement.
After the + in the SELECT query you can place your own query. This example is just extremely useful in my next post :)

Make sure that you do grant access to the correct people in order to use this function. In the case of a CRM report, add the reporting group. See the below example, but make sure that you do change the guid to the guid which is valid for your system.

GRANT EXECUTE ON [dbo].[fn_PGGM_GetActivityPartyList] TO [PGGM-INTRA\ReportingGroup {05e0584a-1d94-424c-8014-c2f3b1b92ccc}]
GO

Happy reporting!