SQL GROUP_CONCAT split in different columns

Question

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.

SQL does not support a dynamic number of columns. You will have to do it in the application. — Vatev, Jul 24 '13 at 14:17
You could use logic in a Cursor to do this. But the cursor would first have to look into how many columns of data your result set needed. The dynamically create a temp table to be populated and later selected. A dynaminc number of columns is the issue with this challenge. — M T Head, Jul 13 '17 at 16:46

Gordon Linoff · Accepted Answer · 2013-07-24T14:24:26.227

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).

@Luv . . . My apologies. I didn't see the SQLFiddle link in the question. The above code is tested and works. — Gordon Linoff, Jul 24 '13 at 14:25
Perfect it works! Thanks for the quick and very correct answer! I'll accept it as an answer is some as the time limit is over :p — Bram Wijns, Jul 24 '13 at 14:28

JGFMK · Answer 2 · 2017-07-12T21:51:33.150

Use this query to work out the number of currency columns you'll 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 prepared statement to generate the result, using Gordon Linoff solution with query result above to in this thread.

score -2 · Answer 3 · answered Jul 24 '13 at 14:20

-2

Ypu can use dynamic SQL, but you will have to use procedure

answered Jul 24 '13 at 14:20

jaczes

1,366
2
8
16

SQL GROUP_CONCAT split in different columns

3 Answers3

Related