SQL GROUP_CONCAT split in different columns
group-concat, mysql, sql
Solution
You can do this with `substring_index()`. The following query uses yours as a subquery and then applies this logic:
select Name, ISOCode_2,
substring_index(currencies, ',', 1) as Currency1,
(case when numc >= 2 then substring_index(substring_index(currencies, ',', 2), ',', -1) end) as Currency2,
(case when numc >= 3 then substring_index(substring_index(currencies, ',', 3), ',', -1) end) as Currency3,
(case when numc >= 4 then substring_index(substring_index(currencies, ',', 4), ',', -1) end) as Currency4,
(case when numc >= 5 then substring_index(substring_index(currencies, ',', 5), ',', -1) end) as Currency5,
(case when numc >= 6 then substring_index(substring_index(currencies, ',', 6), ',', -1) end) as Currency6,
(case when numc >= 7 then substring_index(substring_index(currencies, ',', 7), ',', -1) end) as Currency7,
(case when numc >= 8 then substring_index(substring_index(currencies, ',', 8), ',', -1) end) as Currency8
from (SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currencies,
count(*) as numc
FROM country
INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id
INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id
GROUP BY country.name
) t
The expression `substring_index(currencies, ',' 2)` takes the list in currencies up to the second one. For American Somoa, that would be `'US Dollar,Kwanza'`. The next call with `-1` as the argument takes the last element of the list, which would be `'Kwanza'`, which is the second element of `currencies`.
Also note that SQL queries return a well-defined set of columns. A query cannot have a variable number of columns (unless you are using dynamic SQL through a `prepare` statement).
Problem
I searched a lot, but didn't find a proper solution to my problem. What do I want to do? I have 2 tables in MySQL: - Country - Currency (I join them together via CountryCurrency --> due to many to many relationship) See this for a working example: http://sqlfiddle.com/#!2/317d3/8/0 I want to link both tables together using a join, but I want to show just one row per country (some countries have multiple currencies, so that was the first problem). I found the group_concat function: ``` SELECT country.Name, country.ISOCode_2, group_concat(currency.name) AS currency FROM country INNER JOIN countryCurrency ON country.country_id = countryCurrency.country_id INNER JOIN currency ON currency.currency_id = countryCurrency.currency_id GROUP BY country.name ``` This has the following result: ``` NAME ISOCODE_2 CURRENCY Afghanistan AF Afghani Åland Islands AX Euro Albania AL Lek Algeria DZ Algerian Dinar American Samoa AS US Dollar,Kwanza,East Caribbean Dollar ``` But what I want now is to split the currencies in different columns (currency 1, currency 2, ...). I already tried functions like MAKE_SET() but this doesn't work.