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
– Vatev, Commented Jul 24, 2013 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
– M T Head, Commented Jul 13, 2017 at 16:46

Gordon Linoff · Accepted Answer · 2013-07-24 14:24:26Z

6

+50

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

edited Jul 24, 2013 at 14:24

answered Jul 24, 2013 at 14:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@Luv . . . My apologies. I didn't see the SQLFiddle link in the question. The above code is tested and works.

Bram Wijns Over a year ago

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

JGFMK · Accepted Answer · 2017-07-12 21:51:33Z

1

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.

edited Jul 12, 2017 at 21:51

answered Jul 12, 2017 at 21:42

JGFMK

8,9974 gold badges65 silver badges101 bronze badges

Comments

jaczes · Accepted Answer · 2013-07-24 14:20:04Z

-2

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

answered Jul 24, 2013 at 14:20

jaczes

1,4042 gold badges9 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL GROUP_CONCAT split in different columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related