Concatenate rows to single row in SQL Server 2008

Question

I'm using MonetDb for analytics, which is using SQL Server 2008 for query processing.

I have a table ROLES, which has data below.

Emp ROLE
1   ROLE_1
1   ROLE_2
1   ROLE_3
2   ROLE_1
2   ROLE_2
3   ROLE_3
3   ROLE_4

I want output as

EMP ROLE
1   ROLE_1, ROLE_2, ROLE_3
2   ROLE_1, ROLE_2
3   ROLE_3, ROLE_4

I tried group_concat, but it is saying

no such aggregate 'group_concat'

Query I tried is below

select
    emp,
    group_concat(role) as wo
from 
    roles 
group by emp

Is there any alternative for group_concat?

Edit:

Please read comments, I'm using monetdb which doesn't support group_concat and xml.

See if this helps you: [how to make a query with group_concat](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Brien Foss, Dec 26 '17 at 06:59
Thanks for the quick reply. I tried this also. I'm getting **syntax error, unexpected FOR, expecting SCOLON in** — Sreenath, Dec 26 '17 at 07:02
@TT. I tried it. but not working tough. **select emp, role + ',' as role from roles FOR XML PATH(''), TYPE** — Sreenath, Dec 26 '17 at 07:06
Possible duplicate of [sql server 2000 the GROUP\_CONCAT() function](https://stackoverflow.com/questions/16618132/sql-server-2000-the-group-concat-function) — TT., Dec 26 '17 at 07:06
...and if you're going to take your pick, pick 2017, because it has [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql) :D — Matt Gibson, Dec 26 '17 at 09:05
@marc_s: Actually it says 2008 but not behaving so. take a look https://www.monetdb.org/Documentation/SQLreference — Sreenath, Dec 26 '17 at 09:41
I can't understand Why people are down voting even before reading comments. I'm mentioning that I already tried all the links provided by you people — Sreenath, Dec 27 '17 at 03:22

score 1 · Accepted Answer · answered Dec 27 '17 at 12:29

1

Monetdb has R integration. You can write a R function.

R function:

CREATE AGGREGATE str_aggre(val STRING) RETURNS STRING LANGUAGE R {
   aggregate(val, by=list(aggr_group), FUN=toString)$x 
};

SQL Command:

select
    emp,
    str_aggre(role) as wo
from 
    roles 
group by emp

This will work.

answered Dec 27 '17 at 12:29

suresh manda

1

Thank you. I tried for R functions also. This helped me. You saved my day. – Sreenath Dec 27 '17 at 12:32

1 Answers1