w tym artykule omówimy operatory SQL Pivot i SQL Unpivot oraz sposoby ich przydatności do transpozycji danych serwera SQL. Omówimy również zarówno statyczne, jak i dynamiczne sposoby używania operatorów relacyjnych PIVOT i UNPIVOT, które można wykorzystać do przekształcenia zagregowanych odrębnych wartości jako kolumn w zestawie wyników, określając wszystkie wartości kolumn w klauzuli PIVOT in.
wprowadzenie do SQL Server pivot
istnieje kilka sposobów transpozycji zbioru danych z wierszy do kolumn i kolumn do wierszy. SQL Pivot jest jedną z technik, która umożliwia transpozycję wierszy do kolumn i wykonuje możliwe agregacje po drodze. Operatory relacyjne SQL PIVOT i SQL UNPIVOT transponują dwuwymiarowe dane o wartości tabelarycznej do innej formy danych. SQL PIVOT transponuje wyrażenie wartości tabeli z unikalnego zestawu wartości z jednej kolumny do wielu kolumn na wyjściu i wykonuje agregacje. SQL UNPIVOT wykonuje odwrotną operację SQL PIVOT, przekształcając kolumny wyrażenia wartości tabeli na wartości kolumn.
w większości przypadków statyczna technika obrotu wystarcza do spełnienia wymagań biznesowych. Na przykład Miesięczna prognoza sprzedaży, roczny podział sprzedaży, kwartalna agregacja sprzedaży itp., w których kolumny klauzuli IN pozostają statyczne. W niektórych innych przypadkach potrzebujemy szczegółowych szczegółów, a wyrażenie wartości tabeli ma charakter bardziej dynamiczny i cały czas nowy zestaw wyrażenia jest zawarty w tabeli, wtedy dynamiczny PIVOT byłby najlepszym wyborem.
Uwaga: w powyższym obrazie widzimy, że w procesie Pivota wartości kolumn są obracane z pionu na poziome, a UNPIVOTOWANIE jest jak obracanie z poziomu na pion.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <alias tabeli przestawnej>
< kolejność według klauzuli>;
wprowadzenie do SQL Pivot
zaczniemy od obracania i od-obracania danych. Już teraz rozumiesz, jakie operatory PIVOT i UNPIVOT są w SQL Server. W prostych słowach, jest to po prostu inny sposób na opisanie procesu przekształcania różnych wartości wierszy do własnych kolumn jest znany jako Pivoting. Proces przekształcania kolumn w Wiersze jest znany jako un-pivoting.
przykład 1: Jak wykonać operację SQL Pivot
Wyobraźmy sobie przykładowy zestaw danych pochodzący z bazy danych AdventureWorks2014. W tym przypadku masz prosty zestaw danych z kolumną SalesYear wzdłuż jednej wartości ilościowej Sumalsales.
teraz zestaw danych jest gotowy do obracania. Masz SalesYear w pierwszej kolumnie, Sumalsales w drugiej kolumnie. Jeśli poprosiłeś o transpozycję danych, musisz wziąć każdy odrębny rok w kolumnie, a te kolumny stają się nagłówkiem kolumn przestawnych. Tak więc 2011 staje się pierwszą kolumną, 2012 staje się własną kolumną i tak dalej. Rzeczywiste wartości z następnej kolumny sumują się, trzymają się nagłówków kolumn obrotowych. Teraz, tabela przestawna będzie wyglądać poniżej.
|
1
2
3
4
5
6
7
8
9
10
|
select * from
(
wybierz rok(SOH.OrderDate) jako SalesYear,
SOH.SubTotal as TotalSales
ze sprzedaży.SalesOrderHeader SOH
dołącz do sprzedaży.SalesOrderDetail SOD ON SOH.SalesOrderId = SOD.SalesOrderId
) AS Sales
PIVOT (SUM(TotalSales)
FOR SalesYear IN (,,,))
as PVT
|
poniższy obraz przedstawia dane wejściowe, składnia pivot, Pivot sql i wyniki wyjściowe.
przykład 2: Jak wykonać operację SQL Unpivot
spójrzmy teraz w innym kierunku, un-pivoting, który jest tak prosty, jak przejście od poziomego do pionowego. W takim przypadku należy wziąć wszystkie te odrębne kolumny, które są zaznaczone, i zamienić te nagłówki kolumn w ich własne wiersze. Obracanie jest jak obracanie z pionu do poziomu. A un-pivoting jest jak obracanie z poziomu do pionu.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
w poniższym przykładzie do odpicowania wartości używany jest obrotowy zestaw danych. Najważniejsze jest to, że łatwo było to zrobić, ponieważ byliśmy w stanie utworzyć tabelę przestawną jako etap pośredni, a następnie wykonać transformację za pomocą operacji unpivot.
Uwaga: operacja SQL pivot powoduje przeniesienie zagregowanego wyniku do kolumny, ale podczas gdy SQL unpivot nie jest dokładnym odwrócenie transpozycji kolumn do segregowanych wartości wierszy. Operator unpivot nie podzieli zagregowanych wyników.
przykład 3: Jak uzyskać sprzedaż kwartalną za pomocą operacji pivot SQL
zobaczmy jeszcze kilka przykładów, aby lepiej zrozumieć koncepcje pivotowania. W takim przypadku zobaczymy, jak uzyskać kwartalne dane Sprzedaży zagregowane na podstawie sprzedaży kwartalnej.
zanim przejdziemy do rozwiązania, zawsze zaleca się pracę nad stworzeniem zestawu danych dla operacji PIVOT.
poniższy przykład przedstawia zagregowany miesięczny spread sprzedaży w ciągu roku sprzedaży.
|
1
2
3
4
5
6
7
8
9
WYBIERZ ROKU (T. zw.Data zamówienia) JAKO roku sprzedaży,
MIESIĄC (T. zw.Data zamówienia) JAKO Miesiąc sprzedaży,
KWOTA (Tj. suma częściowa) JAKO sumy sprzedaży
OD sprzedaży.SalesOrderHeader Sox
dołącz do sprzedaży.Zamówienie sprzedaży wyszczególnia SOD na SOH.Identyfikator zamówienia = SOD.Identyfikator zamówienia
Grupa według roku (COX.OrderDate),
miesiąc(SOH.OrderDate)
kolejność według roku (SOH.OrderDate),
miesiąc(SOH.Poniżej przedstawiono dane bazowe
gdy będziesz gotowy z danymi bazowymi, możesz zastosować operator pivot. W poniższym przykładzie, klauzula IN akceptuje Nie numeryczny znak, który został wygenerowany na podstawie wartości DATEPART. Wartość DATEPART jest łączona z 'Q’, A następnie wartości są przekazywane do klauzuli IN operatora PIVOT.
poniższy wynik to kwartalny podział danych sprzedaży
 przykład 4: Jak uzyskać miesięczną sprzedaż za pomocą operacji pivot SQLprzyjrzyjmy się innemu przykładowi, aby zobaczyć dane podziału sprzedaży na podstawie każdego miesiąca. W poniższym przykładzie wartości klauzuli IN są generowane jako część funkcji DATEPART. Wartości DATEPART są przekazywane do klauzuli IN operatora PIVOT.
poniższy wynik to miesięczny podział danych sprzedaży
przykład 5: Jak uzyskać miesięczną sprzedaż za pomocą dynamicznej operacji pivot SQLpozwól nam adres miesięczny podział danych sprzedaży za pomocą dynamicznego obrotu. Do tej pory omawialiśmy statyczne operacje obrotu. Aby skonwertować statyczną operację obrotu na dynamiczną, musimy usunąć zakodowane na stałe wartości z klauzuli IN. Najpierw pobierz odrębne wyrażenie ze zbioru danych, a następnie przygotuj ciąg poprzez połączenie wszystkich wyrażeń. W poniższym przykładzie nazwa kolumny @jest używana do połączenia całego wyrażenia. Skonkatenowane wyrażenie jest przekazywane do klauzuli pivot IN. Reszta to prosta konwersja statycznego SQL na dynamiczny SQL i wywołanie @DML przy użyciu procedury składowanej sp_executesql.
To wszystko na razie… Podsumowującdo tej pory zademonstrowaliśmy podstawowe pojęcia i kilka przykładów SQL Pivot i SQL unpivot. Próbka jest oparta na bazie danych SQL Server adventureworks2014. Możesz spróbować i przetestować wszystkie próbki samodzielnie. Jeśli masz jakiekolwiek pytania, prosimy o komentarz poniżej…
 jestem technologiem baz danych z ponad 11-letnim bogatym, praktycznym doświadczeniem w technologiach bazodanowych. Posiadam certyfikat Microsoft Certified Professional i posiadam Dyplom Master of Computer Application.
moją specjalnością jest projektowanie & wdrażanie rozwiązań wysokiej dostępności i wieloplatformowa migracja DB. Obecnie stosowane technologie to SQL Server, PowerShell, Oracle i MongoDB. Zobacz wszystkie posty Prashanth Jayaram najnowsze posty Prashanth Jayaram (Zobacz wszystkie)
|
