wprowadzenie
w moim ostatnim artykule „Konwersja wartości rozdzielonych przecinkami na wiersze i Vice Versa w SQL Server”, mówiłem o tym, jak można przekonwertować wartości rozdzielone przecinkami (lub oddzielone innym znakiem) w jednej kolumnie na wiersze i vice versa. W tym artykule pokażę, jak można konwertować wartości wierszy na wartości kolumn (PIVOT) i wartości kolumn na wartości wierszy (UNPIVOT) w SQL Server.,
Konwersja wierszy na kolumny-PIVOT
SQL Server ma Operator relacyjny PIVOT, który zamienia unikalne wartości określonej kolumny z wielu wierszy na wiele wartości kolumn na wyjściu (cross-tab), skutecznie obracając tabelę. Umożliwia również wykonywanie agregacji, tam gdzie jest to wymagane, dla wartości kolumn, które są oczekiwane w końcowym wyjściu. Podstawowa składnia operatora relacyjnego Pivota wygląda następująco:
za pomocą poniższego skryptu stwórzmy tabelę i załadujmy do niej dane., Jak widać na poniższym obrazku, ma informacje o sprzedaży dla niektórych krajów przez kilka lat. Ponadto, jeśli zauważysz, dla każdego kraju i dla każdego roku jest oddzielny wiersz.
teraz za pomocą operatora PIVOT przekonwertujemy wartości wierszy na wartości kolumn za pomocą skryptu podanego poniżej i wyników pokazanych na poniższym obrazku.
chociaż użyliśmy funkcji sumowania SUM, w tym przypadku nie ma sumowania, ponieważ istnieje tylko jeden wiersz dla każdej unikalnej kombinacji dla kraju i roku., Należy zwrócić uwagę na użycie nawiasów dla wartości kolumn obrotowych; są one wymagane.
wprowadzenie dynamizmu do obrotu
Jeśli zauważysz w powyższym skrypcie, dostarczyliśmy wartości (2005, 2006, 2007 i 2008) dla kolumn obrotu, ponieważ wartości te są dostępne w oryginalnych zestawach danych. Ale co, jeśli w przyszłości spodziewane są pewne dodatkowe wartości, na przykład w 2009 i 2010 r. itp. do kolumny obrotowej?,
w takim przypadku możesz nadal używać wartości kolumn pivot, które mają pojawić się (lub które nadal nie są dostępne w oryginalnym zbiorze danych) w przyszłości, chociaż zobaczysz wartość NULL dla jej wartości. Poniższy skrypt pokazuje ten scenariusz, a poniższy obraz pokazuje NULL dla LAT 2009 i 2010, ponieważ nie ma danych dla tych lat.
powyższe podejście działa dobrze, jeśli znasz już wszystkie możliwe wartości dla kolumny obrotu, ale co, jeśli nie?,
W takim przypadku możesz napisać dynamiczne zapytanie, aby najpierw pobrać wszystkie unikalne wartości dla kolumny pivot w trybie runtime, a następnie napisać dynamiczne zapytanie, aby wykonać je za pomocą zapytania pivot, jak pokazano poniżej:
Konwersja kolumn na wiersze – UNPIVOT
UNPIVOT jest innym operatorem relacyjnym w SQL Server, który wykonuje prawie odwrotną operację PIVOT, obracając wartości kolumn do wartości wierszy. Pozwól, że zademonstruję to na przykładzie; stwórzmy tabelę z danymi z poprzedniego zapytania za pomocą poniższego skryptu., Poniższy obrazek pokazuje dane nowo utworzonej tabeli z danymi obrotowymi.
aby obrócić wartości kolumn do wartości wierszy, użyjemy operatora UNPIVOT, jak pokazano poniżej. Poniższy obrazek pokazuje obrócone dane:
jeśli odwołujesz się wstecz, powiedziałem, że UNPIVOT jest prawie odwrotnością operatora PIVOT; oznacza to, że może, ale nie może być dokładnie taki sam. Powodem jest to, że Pivot wykonuje agregację podczas obracania wartości wierszy do wartości kolumn i może scalić możliwe wartości wielu wierszy do wartości pojedynczej kolumny na wyjściu., Na przykład, rozważmy dla danego kraju i roku istnieją dwie wartości, powiedzmy 5000 i 6000. Teraz, gdy go obracasz, wyjście będzie miało wartość 11000 jako wartość kolumny, jeśli masz SUM jako funkcję agregacji. Później, jeśli chcesz go odpiwotować, otrzymasz 11000, a nie wartości bi-furcated (5000 i 6000 jako oryginalne). Mówiąc to, możemy powiedzieć, że jeśli wartości obrotowe są wartościami zagregowanymi, nie będzie możliwe odzyskanie oryginalnych danych.,
podsumowanie
w tym artykule zademonstrowałem, jak można konwertować wartości wierszy na wartości kolumn (PIVOT) i wartości kolumn na wartości wierszy (UNPIVOT) w SQL Server. Mówiłem również o pisaniu dynamicznego zapytania, aby napisać dynamiczne zapytanie przestawne, w którym możliwe wartości kolumn przestawnych są określane w czasie wykonywania.