CSV in SqlServer 2005

I have a table

开发者_如何转开发columns:Name,Role,Dept

A person can have many roles. So while retrieving the data i want the roles column to be in CSV if a person has more than one role.

 Name Role          Dept 
 jose Role1,Role2   PWD

Assuming all other details except the roles for the records are same.

Thanks

This will do the trick for you.

select
    t3.Name,
    left(t3.Roles, len(t3.Roles)-1) as Roles,
    t3.Dept
from
    (select
            t2.Name,
            t2.Dept,
            (select t1.[Role] + ','
             from Table1 as t1
             where
                t1.Name = t2.Name and
                t1.Dept = t2.Dept
             order by t1.[Role]
             for xml path('')) as Roles
        from Table1 as t2
        group by t2.Name, t2.Dept) as t3

This is taken from the first example in "The blackbox XML methods" from this page https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

The inner query builds the list of Roles as an xml. The xml path('') parameter is empty and therefor you do not get the xml markup but only the comma separated list.

The outer query is only there to remove the comma at the end of each line.

Group by t2.Name, t2.Dept makes sure that you will only get one row for each unique combination of Name+Dept.

One option is to do it with a recursive parameter and a cursor. Try with this query:

DECLARE @Name varchar(50), @Role varchar(MAX), @Dept varchar(50)

DECLARE role_cursor CURSOR FOR 
SELECT  Name,min(Dept)
FROM    [dbo].[Table_1]
group by Name

OPEN role_cursor;
FETCH NEXT FROM role_cursor 
INTO @Name, @Dept

WHILE @@FETCH_STATUS = 0
BEGIN
    set     @Role = ''
    select  @Role = @Role+[Role]+','
    FROM    [dbo].[Table_1]
    where   Name=@Name

    select  @Name,left(@Role,len(@Role)-1),@Dept

    FETCH NEXT FROM role_cursor 
    INTO @Name, @Dept
END
CLOSE role_cursor;
DEALLOCATE role_cursor;

Once you have it you can use an insert before the select to insert values in a result table

You can create a user-defined aggregate function that produces a comma-separated list of values and can be used in queries as any other aggregate function (i.e. MIN, MAX, SUM, etc).

Here's a relevant post from my blog: A SQL CLR user-defined aggregate - notes on creating and debugging

After you create the dbo.Concat function, you will be able to execute the following query:

SELECT
  Name
, Dept
, dbo.Concat(Role) AS Roles
FROM dbo.Table_1
GROUP BY
  Name
, Dept

I have used it in production environment for a few years and its performance is really good, even with large number of processed rows.