Dodam nieco dłuższe i bardziej szczegółowe wyjaśnienie kroków, które należy podjąć, aby rozwiązać ten problem. Przepraszam, jeśli to za długo.
Zacznę od bazy, którą podałeś, i użyję jej do zdefiniowania kilku terminów, których użyję do końca tego postu. To będzie tabela bazowa :
select * from history;
+--------+----------+-----------+
| hostid | itemname | itemvalue |
+--------+----------+-----------+
| 1 | A | 10 |
| 1 | B | 3 |
| 2 | A | 9 |
| 2 | C | 40 |
+--------+----------+-----------+
To będzie nasz cel, ładna tabela przestawna :
select * from history_itemvalue_pivot;
+--------+------+------+------+
| hostid | A | B | C |
+--------+------+------+------+
| 1 | 10 | 3 | 0 |
| 2 | 9 | 0 | 40 |
+--------+------+------+------+
Wartości w history.hostidkolumnie staną się wartościami Y w tabeli przestawnej. Wartości w history.itemnamekolumnie staną się wartościami x (z oczywistych powodów).
Kiedy muszę rozwiązać problem związany z tworzeniem tabeli przestawnej, rozwiązuję ten problem, stosując trzyetapowy proces (z opcjonalnym czwartym krokiem):
- wybierz kolumny interesów, czyli wartości y i x-wartości
- rozszerz tabelę podstawową o dodatkowe kolumny - po jednej dla każdej wartości x
- grupuj i agreguj tabelę rozszerzoną - jedna grupa na każdą wartość y
- (opcjonalnie) utrwalić zagregowaną tabelę
Zastosujmy te kroki do twojego problemu i zobaczmy, co otrzymamy:
Krok 1: wybierz interesujące kolumny . W pożądanym wyniku hostidpodaje wartości y i itemnamepodaje wartości x .
Krok 2: przedłuż tabelę podstawową o dodatkowe kolumny . Zwykle potrzebujemy jednej kolumny na wartość x. Przypomnijmy, że nasza kolumna wartości x to 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 |
+--------+----------+-----------+------+------+------+
Pamiętaj, że nie zmieniliśmy liczby wierszy - po prostu dodaliśmy dodatkowe kolumny. Zwróć również uwagę na wzorzec NULLs - wiersz z itemname = "A"ma wartość inną niż null dla nowej kolumny Ai wartości null dla innych nowych kolumn.
Krok 3: grupuj i agreguj tabelę rozszerzoną . Musimy to zrobić group by hostid, ponieważ zapewnia wartości 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 |
+--------+------+------+------+
(Pamiętaj, że teraz mamy jeden wiersz na wartość y). Dobra, już prawie jesteśmy! Musimy tylko pozbyć się tych brzydkich NULL.
Krok 4: upiększ . Zamierzamy po prostu zastąpić dowolne wartości zerowe zerami, aby zestaw wyników był ładniejszy:
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 |
+--------+------+------+------+
I gotowe - zbudowaliśmy ładną, ładną tabelę przestawną za pomocą MySQL.
Uwagi dotyczące stosowania tej procedury:
- jakiej wartości użyć w dodatkowych kolumnach. Użyłem
itemvaluew tym przykładzie
- jakiej „neutralnej” wartości użyć w dodatkowych kolumnach. Użyłem
NULL, ale może to być 0lub "", w zależności od twojej dokładnej sytuacji
- jakiej funkcji agregującej należy użyć podczas grupowania. Użyłem
sum, ale counti maxczęsto też jest używany ( maxjest często używany podczas budowania jednorzędowych „obiektów”, które zostały rozmieszczone w wielu rzędach)
- za pomocą wielu kolumn dla wartości y. To rozwiązanie nie ogranicza się do użycia jednej kolumny dla wartości y - wystarczy podłączyć dodatkowe kolumny do
group byklauzuli (i nie zapomnij o selectnich)
Znane ograniczenia:
- to rozwiązanie nie pozwala na n kolumn w tabeli przestawnej - każda kolumna przestawna musi być dodana ręcznie podczas rozszerzania tabeli podstawowej. Dla 5 lub 10 wartości x to rozwiązanie jest dobre. Za 100, nie tak miło. Istnieje kilka rozwiązań z procedurami przechowywanymi generującymi zapytanie, ale są one brzydkie i trudne do prawidłowego wykonania. Obecnie nie znam dobrego sposobu rozwiązania tego problemu, gdy tabela przestawna musi mieć wiele kolumn.
