CODEWITHSUNDEEP
mysqlsql
sp00m
sp00m

Reputation: 48807

How to create new comma-separated column from existing data?

We have the following account table (MySQL RDBMS):

+----+-------------------+----------------+-----------------+-----------------+
| id |     username      | admin_for_blog | editor_for_blog | author_for_blog |
+----+-------------------+----------------+-----------------+-----------------+
|  1 | author_only       | NULL           | NULL            | 1               |
|  2 | editor_and_author | NULL           | 2               | 2               |
|  3 | admin_only        | 3              | NULL            | NULL            |
+----+-------------------+----------------+-----------------+-----------------+

We are migrating to a role-based authorization, so we need to find the new roles for the above existing data. Roles are: AUTHOR, EDITOR and ADMIN. A user can be linked to multiple roles. Roles will be stored as comma-separated values (order doesn't matter). In the above data, the output should then be:

+----+-------------------+---------------+----------------+-----------------+-----------------+
| id |     username      | roles         | admin_for_blog | editor_for_blog | author_for_blog |
+----+-------------------+---------------+----------------+-----------------+-----------------+
|  1 | author_only       | AUTHOR        | NULL           | NULL            | 1               |
|  2 | editor_and_author | AUTHOR,EDITOR | NULL           | 2               | 2               |
|  3 | admin_only        | ADMIN         | 3              | NULL            | NULL            |
+----+-------------------+---------------+----------------+-----------------+-----------------+

So basically:

Any hint on how to to this? I've been able to attach one role per user, but I can't find a way to manage multiple roles separated by commas:

select username,
    case admin_for_blog is not null when true then 'ADMIN' else
    case editor_for_blog is not null when true then 'EDITOR' else
    case author_for_blog is not null when true then 'AUTHOR' else
    'error!' end end end
from account

Upvotes: 0

Views: 72

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the function concat_ws which skips the null values.

select username,
concat_ws(','
         ,case when admin_for_blog is not null then 'ADMIN' end
         ,case when editor_for_blog is not null then 'EDITOR' end
         ,case when author_for_blog is not null then 'AUTHOR' end
         )
from account

Upvotes: 3

Related Questions