Separating SQL GROUP_CONCAT in different columns

Question

I searched a lot, but did not find the right solution to my problem.

What do I want to do?

I have 2 tables in MySQL: - Country - Currency (I join them together through CountryCurrency → due to many relationships)

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 only one row for each country (in some countries there are several currencies, so this 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 now I want to split the currency in different columns (currency 1, currency 2, ...). I already tried functions like MAKE_SET (), but this does not work.

Answer 1

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 a list in currencies up to the second. For American Somoa, it will be 'US Dollar,Kwanza' . The next call with -1 as an argument takes the last element of the list, which will 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 use dynamic SQL through the prepare statement).

Answer 2

Use this query to determine the number of currency columns that you will need:

 SELECT MAX(c) FROM ((SELECT count(currency.name) AS c 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) as t) 

Then dynamically create and execute the prepared statement to generate the result using the Gordon Linoff solution with the query result above in this thread.

Answer 3

Ypu may use dynamic SQL, but you will have to use a procedure