One table, two column MYSQL query question

Question

Consider a MYSQL table called "comments" with 2 fields, "hostname" and "username".

How do I return a list sorted by hostname where the 2nd column is a list of usernames associated with each hostname? I can see how to do this with a scripting language like py, but is it possible as a standard sql qurey?

If I do "SELECT hostname,count(distinct(username)) FROM comments GROUP BY hostname" I get almost the right result, except It only gives me the number of usernames associated with each hostname instead of the actual list of usernames associated with each hostname... trying distinct(username) without the count() around it returns a syntax error.

Answer 1

I think the cleanest way to do this is to just select distinct rows and reformat it in the client if necessary:

SELECT DISTINCT hostname, username
FROM comments
ORDER BY hostname, username

Alternatively if you want the results concatenated you can use GROUP_CONCAT:

SELECT hostname, GROUP_CONCAT(DISTINCT username) as usernames
FROM comments
GROUP BY hostname

Answer 2

You have to add a new user-defined aggregate function, which concatenates the collected username strings.

I would give a shot at this: http://www.codeproject.com/KB/database/mygroupconcat.aspx

Answer 3

SELECT HOSTNAME,
      RTRIM(
          REPLACE(
              REPLACE(XMLAGG(XMLELEMENT("x", USERNAME)).getstringval(), '<x>', NULL),
              '</x>',
              ','
          ),
          ','
      ) LIST_OF_USERNAMES
FROM COMMENTS
GROUP BY HOSTNAME;

Example:

If the source table is:

HOSTNAME     USERNAME
10.12.110.80 GOOFY
10.12.110.80 MINNIE
10.12.110.81 STAR
10.12.110.81 MOON
10.12.110.81 SUN
10.12.110.82 MARMELADE
10.12.110.82 ORANGE
10.12.110.82 JUICE
10.12.110.82 LEMON

the output will be the following:

HOSTNAME     LIST_OF_USERNAMES
10.12.110.80 GOOFY,MINNIE
10.12.110.81 STAR,MOON,SUN
10.12.110.82 MARMELADE,ORANGE,JUICE,LEMON

It works fine in Oracle 10G R2.