MySQL Merge multiple colums into one 'temporary' column

Question

I was curious about the fact, if it's possible to merge multiple columns (seperated by a comma, or something else) into one column.

So for instance, i have these tables (languages):

language_id    |   language_iso  
    1                   NL
    2                   EN
    3                   GT
    4                   EN

(Domains)

domain_id    |   domain_name  
    1                example

And another table which links the language to a webdomain

languagetodomain_id    |   languagetodomain_lang    |   languagetodomain_domain
    1                               1                            1
    2                               2                            1
    3                               4                            1
    4                               3                            1

And retrieve them in this order (ONE column as a reminder)

domain_id    |   domain_name    | TemporaryColumn
    1                example         {1,2,4,3}

[MySQL Results as comma separated list](http://stackoverflow.com/questions/662207/mysql-results-as-comma-separated-list) — Lukasz Szozda, Apr 07 '16 at 20:21
Try using http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html. — gr1zzly be4r, Apr 07 '16 at 20:23
Yeah, but how repeat it for every single row, into one column? — Testuser075, Apr 07 '16 at 20:23
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat group by domain_ID — xQbert, Apr 07 '16 at 20:27

Jester · Accepted Answer · 2016-04-07T20:30:29.860

2

This should work:

SELECT Domains.*, CONCAT('{',GROUP_CONCAT(languagetodomain_lang),'}') AS TemporaryColumn
FROM Domains
JOIN LanguageToDomain ON LanguageToDomain.languagetodomain_domain = Domains.domain_id
GROUP BY domain_id

The function GROUP_CONCAT groups values separating them by a comma. I added the brackets using normal CONCAT function. You need to group on the domain_id so it takes all the languagetodomain_lang for each domain that you have.

I've guessed the table name for languagetodomain as you didn't provide it.

edited Apr 07 '16 at 20:30

answered Apr 07 '16 at 20:23

Jester

1,408
1
9
21

I keep receiving this message that I can only return 1 row. So how will this fix the multiple row issue? – Testuser075 Apr 07 '16 at 20:29
sorry i've mixed up group_concat with concat_ws let me fix it first – Jester Apr 07 '16 at 20:30
fixed, and i don't really see where you would get that error. can you update to see what you have now? – Jester Apr 07 '16 at 20:33
My fault, forgotten to mention that the data is being retrieved due usage of subquery. – Testuser075 Apr 07 '16 at 20:36
have you fixed it yourself? if not, update your question and i'll take another look – Jester Apr 07 '16 at 20:37
I'll be updating my question, one second. – Testuser075 Apr 07 '16 at 20:38
if you can export it as sql, you can make an sql fiddle or just post the creates and inserts. makes it a lot easier to try ^^ – Jester Apr 07 '16 at 20:42
I'm sorry! I managed to fix it yes, due help of you. Thanks, once again.. too bad i can't vote you up :/ – Testuser075 Apr 07 '16 at 21:35
@Testuser075 haha, doesn't matter. happy to help :) you're almost there 1 more rep i think? good luck on your project! – Jester Apr 07 '16 at 21:54

xQbert · Answer 2 · 2016-04-07T20:40:39.693

0

Spelled out:

Group_Concat allows you to combine data across rows.

SELECT D.Domain_Id
  , D.Domain_Name
  , Group_Concat(LanguageToDomain_Lang, 
        ORDER BY LanguageToDomain_ID DESC SEPARATOR ',') as TemporaryColumn
FROM Domains D
INNER JOIN WebDomain WD
 on D.Domain_ID = WD.LanguageToDomain_ID
GROUP BY D.Domain_Id, D.Domain_Name

edited Apr 07 '16 at 20:40

answered Apr 07 '16 at 20:34

xQbert

34,733
2
41
62

MySQL Merge multiple colums into one 'temporary' column

2 Answers2