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.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With