MySQL - строки в столбцы

Я пытался искать сообщения, но я нашел решения для SQL Server/Access. Мне нужно решение в MySQL (5.X).

У меня есть таблица (называемая историей) с тремя столбцами: hostid, itemname, itemvalue.
Если я сделаю select (select * from history), он вернет

   +--------+----------+-----------+
   | hostid | itemname | itemvalue |
   +--------+----------+-----------+
   |   1    |    A     |    10     |
   +--------+----------+-----------+
   |   1    |    B     |     3     |
   +--------+----------+-----------+
   |   2    |    A     |     9     |
   +--------+----------+-----------+
   |   2    |    c     |    40     |
   +--------+----------+-----------+

Как мне запросить базу данных, чтобы вернуть что-то вроде

   +--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

Ответ 1

Я собираюсь добавить несколько более длительное и подробное объяснение шагов, которые необходимо предпринять для решения этой проблемы. Я прошу прощения, если он слишком длинный.

Я начну с базы, которую вы дали, и используйте ее, чтобы определить пару терминов, которые я буду использовать для остальной части этого сообщения. Это будет базовая таблица:

select * from history;

+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
|      1 | A        |        10 |
|      1 | B        |         3 |
|      2 | A        |         9 |
|      2 | C        |        40 |
+--------+----------+-----------+

Это будет нашей целью: символьная сводная таблица:

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

Значения в столбце history.hostid станут y-values  в сводной таблице. Значения в столбце history.itemname станут x-значениями (по понятным причинам).

Когда мне приходится решать проблему создания сводной таблицы, я решаю ее с помощью трехэтапного процесса (с необязательным четвертым шагом):

выберите интересующие столбцы, то есть y-values  и x-values 
расширьте базовую таблицу дополнительными столбцами - по одному для каждого x-значения
и агрегировать расширенную таблицу - по одной группе для каждого y-value
(необязательно) префикс агрегированной таблицы

Давайте применим эти шаги к вашей проблеме и посмотрим, что получим:

Шаг 1: выберите интересующие столбцы. В желаемом результате hostid предоставляет y-значения и itemname предоставляет x-values .

Шаг 2: добавьте базовую таблицу с дополнительными столбцами. Обычно нам нужен один столбец за x-значение. Напомним, что наш столбец x-значений равен itemname:

create view history_extended as (
  select
    history.*,
    case when itemname = "A" then itemvalue end as A,
    case when itemname = "B" then itemvalue end as B,
    case when itemname = "C" then itemvalue end as C
  from history
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

Обратите внимание, что мы не изменили количество строк - мы просто добавили дополнительные столбцы. Также обратите внимание на шаблон NULL - строка с itemname = "A" имеет ненулевое значение для нового столбца A и нулевые значения для других новых столбцов.

Шаг 3: группируйте и группируйте расширенную таблицу. Нам нужно group by hostid, так как оно предоставляет значения y:

create view history_itemvalue_pivot as (
  select
    hostid,
    sum(A) as A,
    sum(B) as B,
    sum(C) as C
  from history_extended
  group by hostid
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

(Обратите внимание, что теперь у нас есть одна строка на y-значение.) Ладно, мы почти там! Нам просто нужно избавиться от этих уродливых NULL s.

Шаг 4: префикс. Мы просто заменим нулевые значения нулями, чтобы результат был приятнее смотреть на:

create view history_itemvalue_pivot_pretty as (
  select 
    hostid, 
    coalesce(A, 0) as A, 
    coalesce(B, 0) as B, 
    coalesce(C, 0) as C 
  from history_itemvalue_pivot 
);

select * from history_itemvalue_pivot_pretty;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

И мы закончили - мы создали хорошую, красивую сводную таблицу с использованием MySQL.

Соображения при применении этой процедуры:

какое значение использовать в дополнительных столбцах. Я использовал itemvalue в этом примере
какое "нейтральное" значение использовать в дополнительных столбцах. Я использовал NULL, но он также может быть 0 или "", в зависимости от вашей точной ситуации.
какую совокупную функцию использовать при группировке. Я использовал sum, но часто используются count и max (max часто используется при построении однострочных "объектов", которые были распространены во многих строках).
с использованием нескольких столбцов для значений y. Это решение не ограничивается использованием одного столбца для значений y - просто подключите дополнительные столбцы к предложению group by (и не забудьте указать select их).

Известные ограничения:

это решение не позволяет n столбцов в сводной таблице - каждый столбец поворота должен быть добавлен вручную при расширении базовой таблицы. Таким образом, для 5 или 10 значений x это решение является приятным. На 100, не очень приятно. Существуют некоторые решения с хранимыми процедурами, генерирующими запрос, но они уродливы и труднодоступны. В настоящее время я не знаю, как правильно решить эту проблему, когда в сводной таблице должно быть много столбцов.

Ответ 2

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

Ответ 3

Другой вариант, особенно полезный, если у вас есть много элементов, которые нужно повернуть, это позволить MySQL построить запрос для вас:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when itemname = ''',
      itemname,
      ''' then itemvalue end),0) AS '',
      itemname, '''
    )
  ) INTO @sql
FROM
  history;
SET @sql = CONCAT('SELECT hostid, ', @sql, ' 
                  FROM history 
                   GROUP BY hostid');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FIDDLE Добавлены некоторые дополнительные значения, чтобы увидеть, как это работает

GROUP_CONCAT имеет значение по умолчанию 1000, поэтому, если у вас действительно большой запрос, измените этот параметр перед его запуском

SET SESSION group_concat_max_len = 1000000;

Тестовое задание:

DROP TABLE IF EXISTS history;
CREATE TABLE history
(hostid INT,
itemname VARCHAR(5),
itemvalue INT);

INSERT INTO history VALUES(1,'A',10),(1,'B',3),(2,'A',9),
(2,'C',40),(2,'D',5),
(3,'A',14),(3,'B',67),(3,'D',8);

  hostid    A     B     C      D
    1     10      3     0      0
    2     9       0    40      5
    3     14     67     0      8

Ответ 4

Воспользовавшись идеей Мэтта Фенвика, которая помогла мне решить проблему (большое спасибо), уменьшите ее до одного запроса:

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

Ответ 5

Я отредактировал Agung Sagita ответ из подзапроса, чтобы присоединиться. Я не уверен, сколько разницы между этими двумя способами, но только для другой справки.

SELECT  hostid, T2.VALUE AS A, T3.VALUE AS B, T4.VALUE AS C
FROM TableTest AS T1
LEFT JOIN TableTest T2 ON T2.hostid=T1.hostid AND T2.ITEMNAME='A'
LEFT JOIN TableTest T3 ON T3.hostid=T1.hostid AND T3.ITEMNAME='B'
LEFT JOIN TableTest T4 ON T4.hostid=T1.hostid AND T4.ITEMNAME='C'

Ответ 6

использовать подзапрос

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

но это будет проблемой, если sub-запрос, полученный больше, чем строка, использует дополнительную агрегированную функцию в подзапросе

Ответ 7

Я делаю это в Group By hostId, тогда он будет показывать только первую строку со значениями,
как:

A   B  C
1  10
2      3

Ответ 8

Мое решение:

select h.hostid, sum(ifnull(h.A,0)) as A, sum(ifnull(h.B,0)) as B, sum(ifnull(h.C,0)) as  C from (
select
hostid,
case when itemName = 'A' then itemvalue end as A,
case when itemName = 'B' then itemvalue end as B,
case when itemName = 'C' then itemvalue end as C
  from history 
) h group by hostid

Он дает ожидаемые результаты в представленном случае.

Ответ 9

Я вычисляю один способ сделать мои отчеты конвертирующими строк в столбцы почти динамическими, используя простые запросы. Вы можете увидеть и протестировать его здесь онлайн.

Число столбцов запроса фиксировано, но значения являются динамическими и основаны на значениях строк. Вы можете построить его. Итак, я использую один запрос для сборки заголовка таблицы, а другой - для просмотра значений:

SELECT distinct concat('<th>',itemname,'</th>') as column_name_table_header FROM history order by 1;

SELECT
     hostid
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue else '' end) as col1
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue else '' end) as col2
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue else '' end) as col3
    ,(case when itemname = (select distinct itemname from history a order by 1 limit 3,1) then itemvalue else '' end) as col4
FROM history order by 1;

Можно также подвести итог:

SELECT
     hostid
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 0,1) then itemvalue end) as A
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 1,1) then itemvalue end) as B
    ,sum(case when itemname = (select distinct itemname from history a order by 1 limit 2,1) then itemvalue end) as C
FROM history group by hostid order by 1;
+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

Результаты RexTester:

http://rextester.com/ZSWKS28923

Для одного из реальных примеров использования в этом отчете ниже показаны в столбцах часы вылета, прибывающие на лодке/автобусе с визуальным графиком. Вы увидите еще один столбец, который не использовался в последнем столбце, не путайте визуализацию: ** система билетов для продажи билетов онлайн и презентаций

Ответ 10

Мне жаль говорить об этом, и, возможно, я не решаю вашу проблему точно, но PostgreSQL на 10 лет старше MySQL и чрезвычайно продвинут по сравнению с MySQL, и есть много способов легко это сделать. Установите PostgreSQL и выполните этот запрос

CREATE EXTENSION tablefunc;

тогда вуаля! А вот обширная документация: PostgreSQL: Документация: 9.1: tablefunc или этот запрос

CREATE EXTENSION hstore;

тогда опять вуаля! PostgreSQL: Документация: 9.0: hstore

Ответ 11

Это не точный ответ, который вы ищете, но это решение, которое мне нужно для моего проекта, и надеюсь, что это поможет кому-то. Это будет перечислять от 1 до n элементов строки, разделенных запятыми. Group_Concat делает это возможным в MySQL.

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

Это кладбище имеет два общих имени, поэтому имена перечислены в разных строках, соединенных одним идентификатором, но с двумя идентификаторами имен, и запрос производит что-то вроде этого

- Appleton, Sulpher Springs 35.4276242832293

Ответ 12

Если бы вы могли использовать MariaDB, есть очень простое решение.

Начиная с MariaDB-10.02 был добавлен новый механизм хранения, называемый CONNECT, который может помочь нам преобразовать результаты другого запроса или таблицы в сводную таблицу, точно так же, как вы хотите: вы можете просмотреть документы.

Прежде всего, установите механизм подключения хранилища.

Теперь столбец сводной таблицы нашей таблицы - это имя itemname а данные для каждого элемента располагаются в столбце стоимости itemvalue, поэтому мы можем получить itemvalue таблицу результатов, используя этот запрос:

create table pivot_table
engine=connect table_type=pivot tabname=history
option_list='PivotCol=itemname,FncCol=itemvalue';

Теперь мы можем выбрать то, что мы хотим, из pivot_table:

select * from pivot_table

Подробнее здесь