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
Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.
Something like:
SELECT country,
MAX( IF( key='President', value, NULL ) ) AS President,
MAX( IF( key='Currency', value, NULL ) ) AS Currency,
...
FROM table
GROUP BY country;
For more info: http://dev.mysql.com/tech-resources/articles/wizard/index.html