SQL Pivot - Dowiedz się, jak konwertować wiersze na kolumny

Relacyjny przechowywać ogromne ilości danych w postaci tabel. Te tabele mogą mieć dowolną liczbę wierszy i kolumn. Ale co by było, gdybyś musiał zmienić dane na poziomie wiersza na dane kolumnowe? Cóż, w tym artykule o SQL Pivot pokażę, jak przekonwertować wiersze na kolumnę w SQL Server.

W tym artykule zostaną omówione następujące tematy:

• Co to jest PIVOT w SQL?
• Składnia
• Przykład
• Działanie klauzuli PIVOT
• SQL UNPIVOT

Co to jest PIVOT w SQL?

Funkcja PIVOT służy do obracania wartości tabeli poprzez konwersję unikatowych wartości jednej kolumny na wiele kolumn. Służy do obracania wierszy do wartości kolumn i uruchamia agregacje, gdy jest to wymagane, na pozostałych wartościach kolumn.

Z drugiej strony UNPIVOT służy do wykonywania odwrotnych operacji. Jest więc używany do konwersji kolumn określonej tabeli na wartości kolumn.

Przechodząc dalej w tym artykule, pozwól nam zrozumieć składnię SQL Pivot.

Składnia:

SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (zapytanie SELECT, które generuje dane) AS [alias dla początkowego zapytania] PIVOT ([AggregationFunction] (ColumName) FOR [ColumnName kolumny, której wartości staną się nagłówkami kolumn] IN ([First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [ostatni kolumna przestawna])) AS [alias tabeli przestawnej]

Tutaj,możesz również użyć Klauzula ORDER BY aby posortować wartości w kolejności rosnącej lub malejącej. Teraz, gdy już wiesz, czym jest PIVOT w SQL i jego podstawowa składnia, przejdźmy dalej i zobaczmy, jak go używać.

Przykłady

Dla lepszego zrozumienia rozważę poniższą tabelę, aby wyjaśnić wszystkie przykłady.

Tabela dostawców:

Napiszmy proste zapytanie, aby uzyskać średni koszt poniesiony przez każdego klienta.

WYBIERZ ID klienta, ŚR (koszt) jako średni koszt klienta z dostawców GRUPA WEDŁUG ID klienta

Wynik:

Teraz powiedzmy, że chcemy obrócić powyższą tabelę. W tym przypadku wartości kolumny CustomerID staną się nagłówkami kolumn.

- Utwórz tabelę przestawną z jednym wierszem i trzema kolumnami WYBIERZ „Średni koszt klienta” AS Koszt_According_To_Customers, [11], [22], [33] FROM (SELECT CustomerID, Cost FROM Suppliers) AS SourceTable PIVOT (AVG (Koszt) FOR CustomerID IN ( [11], [22], [33])) AS PivotTable

Wynik:

Uwaga: Kiedy używasz funkcje agregujące w przypadku PIVOT wartości null nie są uwzględniane podczas obliczania agregacji.

Cóż, to był podstawowy przykład, ale zrozummy teraz, jak działała klauzula PIVOT.

Działanie klauzuli PIVOT

Jak możesz wspomnieć powyżej, aby utworzyć tabelę przestawną, musisz wykonać poniższe kroki:

• Wybierz kolumny do przestawienia
• Następnie wybierz tabelę źródłową.
• Zastosuj operator PIVOT, a następnie użyj funkcji agregujących.
• Wspomnij o wartościach przestawnych.

Wybierz kolumny do przestawienia

Na początku musimy określić pola, które mają być uwzględnione w naszych wynikach. W naszym przykładzie rozważyłem kolumnę AverageCostofCustomer w tabeli przestawnej. Następnie utworzyliśmy trzy inne kolumny z nagłówkami 11, 22 i 33. Przykład-

WYBIERZ „AverageCostofCustomer” AS Cost_According_To_Customers, [11], [22], [33]

Wybierz tabelę źródłową

Następnie musisz określić instrukcję SELECT, która zwróci dane źródłowe dla tabeli przestawnej. W naszym przykładzie zwracamy identyfikator klienta i koszt z tabeli dostawcy.

(WYBIERZ ID klienta, koszt od dostawców) jako tabela źródłowa

Zastosuj operator PIVOT, a następnie użyj funkcji agregujących

Następnie musisz określić funkcję agregującą, która ma być używana podczas tworzenia tabeli przestawnej. W naszym przykładzie użyłem funkcji AVG do obliczenia średniego kosztu.

PIVOT (ŚREDNIA (koszt)

Wspomnij o wartościach przestawnych

Na koniec należy wspomnieć o wartościach, które muszą zostać uwzględnione w wynikowej tabeli przestawnej. Te wartości zostaną użyte jako nagłówki kolumn w tabeli przestawnej.

FOR CustomerID IN ([11], [22], [33])) AS PivotTable

Tak działają operatory PIVOT. Przechodząc dalej w tym artykule na temat SQL PIVOT, pozwól nam zrozumieć, jak różni się on od SQL UNPIVOT.

SQL UNPIVOT

Operator SQL UNPIVOT służy do wykonywania operacji odwrotnej do operacji PIVOT. Służy do obracania danych kolumny do danych na poziomie wiersza. Składnia UNPIVOT jest podobna do składni PIVOT. Jedyną różnicą jest to, że musisz użyć rozszerzenia ' UNPIVOT ” .

Przykład:

Utwórzmy tabelę z kolumnami IDDostawcy, AAA, BBB i CCC. Wstaw też kilka wartości.

CREATE TABLE sampletable (SupplierID int, AAA int, BBB int, CCC int) GO INSERT INTO sampletable VALUES (1,3,5,6) INSERT INTO sampletable VALUES (2,9,2,8) INSERT INTO sampletable VALUES (3, 8,1,7) GO

Wynik:

Teraz powiedzmy, że chcemy cofnąć tabelę. Aby to zrobić, możesz skorzystać z następującego kodu:

WYBIERZ ID dostawcy, klienci, produkty Z (WYBIERZ DOSTAWCĘD, AAA, BBB, CCC FROM sampletable) p UNPIVOT (produkty DLA klientów w (AAA, BBB, CCC)) JAK NA PRZYKŁAD

W ten sposób możesz użyć SQL PIVOT i UNPIVOT. Na tym kończymy ten artykuł. Mam nadzieję, że zrozumiałeś, jak używać SQL. Jeśli chcesz dowiedzieć się więcej o MySQL i poznaj tę relacyjną bazę danych typu open source, a następnie sprawdź naszą który obejmuje szkolenie na żywo prowadzone przez instruktora i rzeczywiste doświadczenie projektowe. To szkolenie pomoże ci dogłębnie zrozumieć MySQL i osiągnąć mistrzostwo w tym temacie.

przestrzeń nazw w języku C ++

Masz do nas pytanie? Wspomnij o tym w sekcji komentarzy tego artykułu na temat SQL Pivot, a skontaktuję się z Tobą.