How to use distinct inside concat_ws() in mysql?

Question

In mySQL I have a table:

id   name   code1    code2    code3
1     Jim    aaa      aaa       a
2     Ryan   bb       bbb       b
3     Ted      c       c       cc

expected output:

id   name   concat_code
1     Jim    aaa/a     
2     Ryan   bb/bbb/b      
3     Ted    c/cc    

I tried the below query:

select id, name,concat_ws("/",NULLIF(code1,""),NULLIF(code2,""),NULLIF(code3,""))as concat_code from table1
group by id  

But it gives wrong output:

id   name   concat_code
1     Jim    aaa/aaa/a     
2     Ryan   bb/bbb/b      
3     Ted    c/c/cc    

How can I use the distinct combination inside concat_ws()?

Answer 1

This is one example of why having columns foo1, foo2, etc is bad database design; there should be a separate table in a one to many relationship with the original table with one foo value in each row. As is, you have to basically emulate that with unions:

select id, name, group_concat(distinct codes.code separator '/')
from table1
join (
    select id,NULLIF(code1,"") as code from table1
    union all
    select id,NULLIF(code2,"") from table1
    union all
    select id,NULLIF(code3,"") from table1
) codes using (id)
group by id  

or use a convoluted expression for code2, code3, etc that returns null if the code matches any of the previous codes in your concat_ws call.

Answer 2

Bit late, but for other people googling: try:

SELECT id, name, 
    CONCAT_WS("/",
        NULLIF(code1,""),
        CASE WHEN code2 NOT IN ('', code1) THEN code2 END, 
        CASE WHEN code3 NOT IN ('', code1, code2) THEN code3 END
    ) AS concat_code
FROM table1

The CASE is functioning as a NULLIF with multiple string values, some being the previous values to prevent duplicates.

I left out the GROUP BY id since id seems to be the primary key, that makes that statement useless.