Mysql, переформатировать данные с длинного/высокого на широкий

У меня есть данные в таблице mysql в формате long/tall (описано ниже) и вы хотите преобразовать ее в широкий формат. Могу ли я сделать это, используя только sql?

Проще всего объяснить с помощью примера. Предположим, у вас есть информация о (стране, ключе, значении) для M-стран, N ключах (например, ключи могут быть доходом, политическим лидером, площадью, континентом и т.д.).

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'

Есть ли способ в SQL создать новую таблицу с данными в широком формате?

select distinct key from table;

//это даст мне все ключи.

1) Как создать таблицу, используя эти ключевые элементы?

2) Как заполнить значения таблиц?

Я уверен, что смогу сделать это на любом языке сценариев (мне нравится python), но мне хотелось знать, есть ли простой способ сделать это в mysql. Многие статистические пакеты, такие как R и STATA, имеют эту команду, потому что она часто используется.

======

Чтобы быть более понятным, вот желаемый входной сигнал для простого случая:

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

Выход

country    President    Currency    newPkey
US         Obama        Dollar      1
China      Hu           Yuan        2

Ответы

Ответ 1

Перекрестные закладки или сводные таблицы - это ответ. Оттуда вы можете выбрать из... INSERT INTO... или создать VIEW из одного SELECT.

Что-то вроде:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

Для получения дополнительной информации: http://dev.mysql.com/tech-resources/articles/wizard/index.html

Ответ 2

Я думаю, что нашел решение, которое использует VIEWS и INSERT INTO (как предложено e4c5).

Вы должны сами получить свой список AttrNames/Keys, но MYSQL делает другой тяжелый подъем.

Для простого тестового примера выше создайте new_table с соответствующими столбцами (не забудьте также иметь первичный ключ с автоматическим приращением). Тогда

CREATE VIEW a
AS SELECT country, attrValue
WHERE attrName="President";

CREATE VIEW b
AS SELECT country, attrValue
WHERE attrName="Currency";


INSERT INTO newtable(country, President, Currency)
SELECT a.country, a.attrValue, b.attrValue
FROM  a
INNER JOIN b  ON a.country=b.country;

Если у вас есть больше attrNames, затем создайте одно представление для каждого из них, а затем соответствующим образом настройте последнее утверждение.

INSERT INTO newtable(country, President, Currency, Capital, Population)
SELECT a.country, a.attrValue, b.attrValue, c.attrValue, d.attrValue
FROM  a
INNER JOIN b  ON a.country=b.country
INNER JOIN c  ON a.country=c.country
INNER JOIN d  ON a.country=d.country;

Несколько советов

используйте NATURAL LEFT JOIN, и вам не нужно указывать предложение ON

Ответ 3

Если вы использовали SQL Server, это было бы легко с помощью UNPIVOT. Насколько мне известно, это не реализовано в MySQL, поэтому, если вы хотите это сделать (и я бы посоветовал это сделать), вам, вероятно, придется генерировать SQL динамически, и это беспорядочно.