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.
