sqlsql-server-2008monetdbmonetdblite

Concatenate rows to single row in SQL Server 2008


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.


Solution

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

    Check this link

    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.