title: Mysql, reshape data from long / tall to wide
body: <p>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?</p>
  
  <p>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.)</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>Is there a way in SQL to create a new table with the data in the wide format?</p>
  
  <pre><code>select distinct key from table;
  </code></pre>
  
  <p>// this will get me all the keys.</p>
  
  <p>1) How do I then create the table using these key elements?</p>
  
  <p>2) How do I then fill in the table values?</p>
  
  <p>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.</p>
  
  <p>======</p>
  
  <p>To be more clear, here is the desired input output for a simple case:</p>
  
  <p>Input:</p>
  
  <pre><code>country    attrName    attrValue     key  (these are column names)
  US         President   Obama         2
  US         Currency    Dollar        3
  China      President   Hu            4
  China      Currency    Yuan          5
  </code></pre>
  
  <p>Output</p>
  
  <pre><code>country    President    Currency    newPkey
  US         Obama        Dollar      1
  China      Hu           Yuan        2
  </code></pre>