I have a table of user group assignments, such as:
ID----UserName------Group
01 johnsmith admin
02 sarahwilles admin
03 johnsmith testuser
I would like to display it as:
UserName-------Groups
johnsmith admin, testuser
sarahwilles admin
Is this possible via SQL?
This is not part of standard SQL, because it's so open-ended, it's a difficult problem to solve in a way that provides a predictable answer. Unlimited numbers of rows are basically normal and expected part of SQL, but unlimited length text strings are not. group_concat() exists in mysql, but to me it is usually a near-miss for my requirements. It does the only thing it can, which is to truncate results if it passes over a system limit, group_concat_max_len. Consequently, it still requires extra code to calculate the length of the "real" result and see whether that is longer than group_concat_max_len. Of course, you may have some other constraints that guarantee it won't be a problem, but more likely, it's merely improbable, not impossible to have a problem from exceeding group_concat_max_len. Additionally, that means you have to verify the value of group_concat_max_len in your code at some point, because it's configurable and could change at some later date.
In almost every case that I've ever wished I could use group_concat(), I ultimately found that I was better off writing something that could work with all the values in rows instead of concatenating them into one string. It can be pretty handy, though, if you're just messing around at the mysql> prompt, exploring your data.
Not in standard SQL, but MySQL** provides GROUP_CONCAT which will do exactly what you want.
SELECT foo, GROUP_CONCAT(DISTINCT bar ORDER BY bar) AS bars
FROM foo_table
LEFT JOIN bar_table ON bar_table.foo_id = foo_table.foo_id
** presuming that's ok, since you've tagged the question with mysql
Do try this:
SELECT UserName, group_concat(group) allGroup
FROM tableAssignment
GROUP BY UserName
Hope it helps.
