Grouping multiple records into a single row of a query

I suppose this is a common problem but I was stuck on this for a while, until I found a helpful article (which I've now lost!) proposing a solution if you're using SQL Server 2000.

Needing to get a single row of data from tables with a one-to-many relationship.

Using an outer join you would get 3 records

However I needed 1 row not 3, like this:

Solution.

In order to get the desired result I created a function. The function queries the table and concatenates the rows together using the Coalesce() function. Here's a version of the query I used:

CREATE FUNCTION dbo.RowsToString
( @USER_ID int )
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @Result Varchar(8000)
SELECT @Result = Coalesce(@Result + ', ', '') + TR.Record FROM tbl_results TR
WHERE TR.USER_ID = @USER_ID
RETURN ( @Result )
END

Then call the function within a query, or cfquery, as follows:


SELECT USER_ID, dbo.RowsToString(USER_ID) AS RECORDS
FROM tbl_users


Result: