mysql data dump formatting

I have been tasked with creating a .sql batch file to query a MySQL database (version: 4.1.13-standard) containing user/server/group information, ex.

user1 server1 group1,group2

user1 server2 group1,group3

user1 server3 group5

user2 server2 group2

user2 server5 group2

user3 server4 group4

The trick is, the output file must be formatted in such a way that there is one line per user with all the server and group information combined. So the example above would look like:

"user1","server1:group1-group2;server2:group1-group3;server1:group5"

"user2","server2:group2;server5:group2"

"user3","server4:group4"

I apologize if this or a similar question has been answered before. I promise I spent hours searching for a solution but did not find one. Or I didn't realize it when I saw it. By trade, I am not a DBA by any stretch of the imagination, and what little I do know about mysql isn't enough to figure this out.

So far, the closest I've gotten is using:

select concat(name,',',group_concat(concat(trim(hostname),':',replace(trim(groups),',','-'))separator ';'))
开发者_StackOverflow

Thanks for any help you wish to provide.

---

I tried a little bit around with one of my databases and I got a result with is rather similar to your expected result, because I've no database that is structurde like yours.

Try

SELECT CONCAT(
  t.name, ',', GROUP_CONCAT(
      DISTINCT CONCAT(TRIM(t.hostname),':', REPLACE(TRIM(t.groups),',','-'))
  SEPARATOR ';')
) FROM table_name t GROUP BY t.name

I guess, DISTINCT and GROUP BY t.name could be the magic words ;)