I have data in a mysql table in long / tall format (described below) and want to convert it to wide format. Can I do this using just sql?
Easiest to explain with an example. Suppose you have information on (country, key, value) for M countries, N keys (e.g. keys can be income, political leader, area, continent, etc.)
Long format has 3 columns: country, key, value
- M*N rows.
e.g.
'USA', 'President', 'Obama'
...
'USA', 'Currency', 'Dollar'
Wide format has N=16 columns: county, key1, ..., keyN
- M rows
example:
country, President, ... , Currency
'USA', 'Obama', ... , 'Dollar'
Is there a way in SQL to create a new table with the data in the wide format?
select distinct key from table;
// this will get me all the keys.
1) How do I then create the table using these key elements?
2) How do I then fill in the table values?
I'm pretty sure I can do this with any scripting language (I like python), but wanted to know if there is an easy way to do this in mysql. Many statistical packages like R and STATA have this command built in because it is often used.
======
To be more clear, here is the desired input output for a simple case:
Input:
country attrName attrValue key (these are column names)
US President Obama 2
US Currency Dollar 3
China President Hu 4
China Currency Yuan 5
Output
country President Currency newPkey
US Obama Dollar 1
China Hu Yuan 2