A table that could have multiple rows for 1 user, I would like to aggregate the multiple rows into 1 line for each distinct userid.
We want the rows to show UserID , Role (separated by semi colon)
In SQL Stuff() function is used. And tried the same SQL syntax but without success
SELECT userid, role = stuff((select ‘;’ + roleid from “aspnet_UsersInRoles.json” for XML PATH (’’)), 1, 1, ‘’)
FROM “aspnet_UsersInRoles.json” group by userid
I am not looking for exact function etc, its to know how we can achieve same result with dremio queries