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.
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.