Concatenating the Results of a Grouped SQL Query

Goal: To concatenate, associated columns of grouped rows, as shown below.

SQL Queries allow for the GROUP BY operator, in instances where you group tuples (rows in a SQL result set) for some aggregate function, more commonly appropriate for numeric values like SUM, AVERAGE etcetera. In the case of String, or Character values, the aggregate functions are limited in scope.

Solving the above problem would require such a function, similar in functionality to a say, CONCAT (criteria), but none such exists. The work around involves the creation of a temporary table, and a dbo function with an output variable. A recursive solution would be more elegant, and any contributions would be appreciated.

Solution

1) Create a temporary Table called Temp as following:

CREATE TABLE [dbo].[junk]( [ID] [varchar](50) , [Column_1] [varchar](50))

Insert into this the first two columns, the first column being the column that is grouped by and the second, the one that needs to be aggregated of the query in question

2) Define a new Scalar Function in MS Sql Server as follow:

CREATE FUNCTION[dbo].[StringAggregate](@ID varchar(10))
returns varchar(5000)
as
begin
declare @out varchar(5000)
select @out = coalesce(@out + ' , ' + convert(varchar,Column_1), convert(varchar,Column_1))
from junk
where ID = @ID
return @out
end

3) Invoke the function as an Aggregate in the following: (Note the use of the output variable Criteria)

select ID, dbo.StringAggregate(ID) Criteria
from
(
select ID
from junk
group by ID
) a