SQL Server PIVOT (Polski)

Podsumowanie: w tym samouczku nauczysz się, jak używać operatora SQL Server PIVOT aby przekonwertować wiersze na kolumny.

Konfiguracja celów

Do demonstracji użyjemy production.products i production.categories tabele z przykładowej bazy danych:

Poniższe zapytanie znajduje liczbę produktów w każdej kategorii produktów:

Oto dane wyjściowe:

Naszym celem jest przekształcenie nazw kategorii z pierwszej kolumny wyniku w wiele kolumn i policzenie liczby produktów w każdej nazwa kategorii jak na poniższym obrazku:

Dodatkowo możemy dodać rok modelowy, aby zgrupować kategorię ory według roku modelowego, jak pokazano w następującym wyniku:

Wprowadzenie do operatora SQL Server PIVOT

SQL Server PIVOT operator obraca wyrażenie wyceniane w tabeli. Przekształca unikalne wartości w jednej kolumnie w wiele kolumn w danych wyjściowych i wykonuje agregacje na wszystkich pozostałych wartościach kolumn.

Wykonaj następujące kroki, aby przekształcić zapytanie w tabelę przestawną:

• Najpierw wybierz podstawowy zestaw danych do przestawienia.
• Po drugie, utwórz tymczasowy wynik, używając tabeli pochodnej lub wspólnego wyrażenia tabelowego (CTE).
• Po trzecie, zastosuj PIVOT operator.

Zastosujmy te kroki w poniższym przykładzie.

Najpierw wybierz nazwę kategorii i identyfikator produktu z production.products i production.categories jako dane podstawowe do przestawiania:

Po drugie, utwórz tymczasowy zestaw wyników przy użyciu tabeli pochodnej:

Po trzecie, zastosuj operator PIVOT:

To zapytanie generuje następujące dane wyjściowe:

Teraz każda dodatkowa kolumna, którą dodasz do listy wyboru zapytania, która zwraca baza dane automatycznie utworzą grupy wierszy w tabeli przestawnej. Na przykład możesz dodać kolumnę roku modelowego do powyższego zapytania:

Oto wynik:

Generowanie wartości kolumn

W powyższym zapytaniu trzeba było wpisać każdą nazwę kategorii w nawiasach po IN ręcznie. Aby tego uniknąć, możesz użyć funkcji QUOTENAME() w celu wygenerowania listy nazw kategorii i skopiowania jej w zapytaniu.

Najpierw wygeneruj listę nazw kategorii:

Wynik będzie wyglądał następująco:

W tym fragmencie:

• Funkcja QUOTENAME() otacza nazwę kategorii w nawiasach kwadratowych, np.
• Funkcja LEFT() usuwa ostatni przecinek z ciągu @columns .

Po drugie, skopiuj listę nazw kategorii z wyniku i wklej ją do zapytania.

Dynamiczne tabele przestawne

Jeśli dodasz nową nazwę kategorii do tabeli production.categories, musisz przepisać zapytanie, co nie jest idealne. Aby tego uniknąć, możesz użyć dynamicznego języka SQL, aby uczynić tabelę przestawną dynamiczną.

W tym zapytaniu zamiast przekazywania stałej listy nazw kategorii do PIVOT, tworzymy listę nazw kategorii i przekazujemy ją do instrukcji SQL, a następnie wykonujemy tę instrukcję dynamicznie, używając procedury składowanej sp_executesql.

W tym samouczku , nauczyłeś się, jak używać tabeli PIVOT programu SQL Server do konwersji wierszy na kolumny.