Alternative to cursors for denormalizing many values into one column?

Question

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?
SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

I have a table similar to this:

DocumentID      Name
1           Jack D'Souza
1           Rick Astley
1           Frankestein
1           Einstein

I want to write a SQL Server scalar function which would accept in parameter as DocumentID and give a CSV separated value. So, If I pass in 1, it would give Jack D'Souza, Rick Astley, Frankestein, Einstein

Inside the function, I can create in cursor and create this CSV list. But is there any alternative to using cursor?

and here: http://stackoverflow.com/questions/6344950/sql-query-to-get-aggregated-result-in-comma-seperators-along-with-group-by-colum/ — Larry Lustig, Oct 02 '12 at 03:35

score 3 · Accepted Answer · answered Oct 02 '12 at 03:40

Have a look at this example

CREATE FUNCTION MyConcat(@ID INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @RetVal VARCHAR(MAX)
    SELECT  @RetVal = stuff(
                    (
                        select  ',' + t1.Name
                        from    MyTable t1
                        where   t1.DocumentID = t.DocumentID
                        for xml path('')
                    ),1,1,'') 
    FROM    MyTable t
    WHERE   t.DocumentID = @ID
    GROUP BY t.DocumentID
  RETURN @RetVal
END;

SQL Fiddle DEMO

You could also create a table function that will return all the values that you need.

Something like

CREATE FUNCTION MyConcatTable()
RETURNS @RetTable TABLE(
    [DocumentID] int, 
    [Name] varchar(MAX)
)
AS
BEGIN
    INSERT INTO @RetTable
    SELECT  t.DocumentID,
            stuff(
                    (
                        select  ',' + t1.Name
                        from    MyTable t1
                        where   t1.DocumentID = t.DocumentID
                        for xml path('')
                    ),1,1,'') 
    FROM    MyTable t
    GROUP BY t.DocumentID
  RETURN
END;

SQL Fiddle DEMO

score 0 · Answer 2 · answered Oct 02 '12 at 03:37

You can create a CLR aggregate function which does this.. Quick search of google found this:

http://www.mssqltips.com/sqlservertip/1691/sql-server-clr-function-to-concatenate-values-in-a-column/

Seems to be in VB.Net, i'm sure there are other examples out there though which are already written as this is a pretty common request

Alternative to cursors for denormalizing many values into one column?

2 Answers2

SQL Fiddle DEMO

SQL Fiddle DEMO