Mysql get user value following user task

user_13747349 0
Viewed 34

i have a table named (Barcode) as follow

id USER_CODE
1 A1-123
2 A2-456
3 A3-123
4 A4-789

i tried separate user from user_code by :

select *,
        substring_index(`barcode`.`user_code,'-',1) AS `user_name` 
from barcode

it gives result as follow :

id USER_CODE user_name
1 A1-123 A1
2 A2-456 A2
3 A3-123 A3
4 A4-789 A4

now :

how can i set table to be like

code A1 A2 A3 A4
123 DONE DONE
456 DONE
789 DONE

any hint would be appreciated ,thank u

mysql
1 Answers

We can try using pivoting logic here:

SELECT
    SUBSTRING_INDEX(user_code, '-', -1) AS code,
    IF(SUM(SUBSTRING_INDEX(user_code, '-', 1) = 'A1') > 0, 'DONE', '') AS A1,
    IF(SUM(SUBSTRING_INDEX(user_code, '-', 1) = 'A2') > 0, 'DONE', '') AS A2,
    IF(SUM(SUBSTRING_INDEX(user_code, '-', 1) = 'A3') > 0, 'DONE', '') AS A3,
    IF(SUM(SUBSTRING_INDEX(user_code, '-', 1) = 'A4') > 0, 'DONE', '') AS A4
FROM yourTable
GROUP BY
    SUBSTRING_INDEX(user_code, '-', -1);

Demo

user_18632290
Related