2

I need to build a SQL query, for MySQL 5.7, if it is possible, to "GROUP BY" uid and title and concat the permissions column and separate with a |? I am saying GROUP BY but I do not know if that is the right term here.

+------+--------+------------------------------------------------+
| uid  | title  | permissions                                    |
+------+--------+------------------------------------------------+
| 1438 | Delete | /_permission_set.php|/process.php|/command.php |
+------+--------+------------------------------------------------+
| 1438 | Remove | /_permission_set.php|/process.php|/command.php |
+------+--------+------------------------------------------------+

I have tried the following SQL (as you can see in this DBFiddle) but it did not work

SELECT uid, CONCAT('|', permissions)
FROM some_table
WHERE uid=1438
GROUP BY uid, permissions;
4
  • 3
    GROUP BY needs in aggregate function which is GROUP_CONCAT(). Also you'd group not by permission but by title.
    – Akina
    Commented Jun 25 at 4:55
  • Aside: MyISAM has been deprecated for ~ a decade now. 5.7 went EOL last year!
    – Vérace
    Commented Jun 25 at 12:05
  • @Vérace there are a lot of old systems which still relies on MyISAM (lock table style) Commented Jun 25 at 12:42
  • Lots of people relied on slavery, doesn't mean it (or people) should be kept! :-)
    – Vérace
    Commented Jun 25 at 18:18

1 Answer 1

4
SELECT uid, title, GROUP_CONCAT(permissions SEPARATOR '|')
FROM some_table
WHERE uid=1438
GROUP BY uid, title;
uid title GROUP_CONCAT(permissions SEPARATOR '|')
1438 Delete /_permission_set.php|/process.php|/command.php
1438 Remove /_permission_set.php|/process.php|/command.php

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.