Pivot i UnPivot w SQL Server

Czym są polecenia Pivot i UnPivot w SQL Server?

Polecenia Pivot i UnPivot możesz skojarzyć na pierwszy rzut oka z tabelami przestawnymi. W SQL Server jest możliwość ustawienia wartości rekordów z danej kolumny jako nazwy kolumn. Możesz też zrobić operację odwrotną, a więc nazwy kolumn zamienić na dane. Pivot i UnPivot to już zaawansowany SQL, jednak warto poznać te techniki, by znacząco uprościć sobie pracę.

W innych językach SQL, jak Presto, czy HQL, podobne konstrukcje nazywają się Lateral View lub Cross Join Unnest. Poniżej zobrazowanie tego, co jesteś w stanie zrobić za pomocą poleceń Pivot i UnPiot w MsSQL.

Zobrazowanie Polecenia Pivot SQL:

MsSQL Pivot, Transact SQL

Zobrazowanie Polecenia UnPivot MsSQL:

MSSQL UnPivot, Transact SQL

Fajne prawda? Najlepsze w tym wszystkim jest to, że nie musisz niepotrzebnie joinować wiele razy tabeli, a zapytanie jest wydajne! W tym artykule poznasz najważniejsze techniki tworzenia tych konstrukcji. Poznasz także metodę tworzenia dynamicznych SQL UnPivot i Pivot w SQL Server.

Pivot i Unpivot w SQL MsSQL – Przykładowe dane

W artykule dowiesz się jak korzystać z poleceń zarówno na danych uproszczonych, jak i danych z bazy AdventureWorks. Dla celów ćwiczenia utwórz prostą tabelę danych i wgraj do niej przykładowe rekordy.

Tworzenie tabeli:

CREATE TABLE [dbo].[Table_1](
[Id] [bigint] NOT NULL,
[BrandName] nvarchar NULL,
[ProductionCountry] nvarchar NULL,
[Quantity] [bigint] NULL)
;

Wgrywanie rekordów:

INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (1, 'BMW', 'Germany', 150000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (2, 'BMW', 'Belgium', 200000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (3, 'Ford', 'France', 50000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (4, 'Ford', 'USA', 350000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (5, 'Ford', 'Germany', 100000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (6, 'Ford', 'Germany', 200000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (7, 'Audi', 'Germany', 1000000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (8, 'Mercedes', 'Germany', 150000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (9, 'Fiat', 'Italy', 520000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (10, 'Fiat', 'Poland', 4700000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (11, 'Seat', 'Germany', 230000);
INSERT INTO [dbo].[Table_1] (ID, BrandName, ProductionCountry, Quantity) VALUES (12, 'Seat', 'Spain', 1000050);
Pivot i UnPivot w MsSQL

Pivot w MsSQL

Ok, masz już do dyspozycji przykładowe dane. Teraz pora na utworzenie skryptu SQL. Z uwagi na to, że konstrukcja nieco odbiega od standardowego SQL, rozpiszę to w trzech krokach. Wynik, który chcemy uzyskać to suma produkcji samochodów, z nazwą marki samochodu w kolumnie (BrandName). Oczywiście zamiast sumy możemy policzyć, średnią, minimum, czy maksimum.

Krok 1: Dane źródłowe

Na początek wskaż, co jest źródłem danych za pomocą polecenia mssql select:

select BrandName, Quantity from [dbo].[Table_1];

Krok 2: Wskazanie nowych kolumn

Wskaż wartości, które mają przyjąć postać kolumny. Wartości wprowadź ręcznie hardkodując je. W kolejnych przykładach dowiesz się, jak wskazywać wartości dynamicznie, bez względu na ich liczbę. Krok 1 i 2 są od siebie oddzielone nawiasami oraz frazą PIVOT. Nazwy bloków SRC i PVT to tylko aliasy, tak więc możesz wykorzystać tutaj inne nazwy. Pamiętaj też o nawiasach kwadratowych.

SELECT [BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat] FROM
(
select BrandName,  Quantity from [dbo].[Table_1]
) as SRC
PIVOT
(
sum(Quantity)
FOR BrandName IN ([BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat])
) as PVT
;

Krok 3: Zapytanie zwracające dane

Wstaw nad zapytaniem polecenie SELECT

SELECT [BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat] FROM
(
select BrandName, Quantity from [dbo].[Table_1]
) as SRC
PIVOT
(
sum(Quantity)
FOR BrandName IN ([BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat])
) as PVT
;

Jeśli wszystko poszło zgodnie z planem, powinieneś uzyskać mniej więcej taki wynik. Oczywiście zamiast sumy możesz użyć innej funkcji agregującej mssql, jak max, min, czy avg.

Pivot w MsSQL – Kurs SQL Server

To nie wszystko, spróbuj rozwinąć swoje zapytanie i w rekordach dodać wartości z kolumny ProductionCountry. Dodaj kolumnę w kroku 1 i 3 zgodnie z poniższym kodem SQL:

SELECT ProductionCountry, [BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat] FROM
(
select BrandName, ProductionCountry, Quantity from [dbo].[Table_1]
) as SRC
PIVOT
(
sum(Quantity)
FOR BrandName IN ([BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat])
) as PVT
;
SQL Server – rekordy w nazwy kolumn

UnPivot w SQL Server

Teraz wyobraź sobie sytuację odwrotną. Masz do dyspozycji dane uporządkowane w kolumnach. Chciałbyś nazwy tych kolumn użyć jako wartości rekordu. Wróć na chwilę do poprzedniego przykładu i utwórz na podstawie wyniku tabelę dbo.Table_2 za pomocą polecenia MsSQL SELECT INTO w następujący sposób:

SELECT ProductionCountry, [BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat]
INTO [dbo].[Table_2]
FROM
(
select BrandName, ProductionCountry, Quantity from [dbo].[Table_1]
) as SRC
PIVOT
(
sum(Quantity)
FOR BrandName IN ([BMW], [Ford], [Audi], [Mercedes], [Fiat], [Seat])
) as PVT
;
SELECT * from [dbo].[Table_2];

Czas na właściwy kod polecenia UnPivot. Podobnie jak w poprzednim przykładzie, zaprezentuję go w trzech krokach:

Krok 1: Wskazanie danych źródłowych

Wskaż dane źródłowe, które chcesz uporządkować jako rekordy.

SELECT ProductionCountry, BMW, Ford, Audi, Mercedes, Fiat, Seat from [dbo].[Table_2];

Krok 2: UnPivot

Określ nazwę dla nowych kolumn. W naszym przykładzie w kolumna z wartością liczbową nazwana zostanie Quantity, a kolumna z wartościami przejętymi po nazwach kolumn źródłowych: CarBrand.

(
SELECT ProductionCountry, BMW, Ford, Audi, Mercedes, Fiat, Seat from [dbo].[Table_2]
) SRC
UNPIVOT
(Qyantity FOR CarBrand IN
(BMW, Ford, Audi, Mercedes, Fiat, Seat)
)AS UPVT

Krok 3: Zapytanie zwracające wynik

Wskaż w nadrzędnym poleceniu SELECT to, co chcesz widzieć jako wynik:

SELECT ProductionCountry, CarBrand, Qyantity FROM
(
SELECT ProductionCountry, BMW, Ford, Audi, Mercedes, Fiat, Seat from [dbo].[Table_2]
) SRC
UNPIVOT
(Qyantity FOR CarBrand IN
(BMW, Ford, Audi, Mercedes, Fiat, Seat)
)AS UPVT
;
UnPivot w SQL Server

Dynamiczny Pivot w SQL Server / Transact-SQL

W powyższych przykładach hardcodowaliśmy wartości kolumn. Były to oczywiście przykłady modelowe i szczerze przyznam, że w realnej pracy każdy podobny skrypt raczej powinieneś zautomatyzować i uodpornić na błędy. Jednym z problemów, który możesz napotkać to oczywiście dodanie dodatkowej wartości do kolumny, której nie przewidziałeś i nie zahadrkodowałeś.

Z pomocą przychodzi nam tutaj dynamiczny Transact-SQL. To, co będziesz musiał zrobić, to zmodyfikować swój kod i użyć zmiennej T-SQL, które przechowa zmieniające się wartości kolumn.

Dynamiczny SQL jest najlepszym sposobem na wykonanie poleceń Pivot. Jest to sposób odporny na zmiany wartości w kolumnie, którą chcesz przekształcić oraz dodanie i usuwanie wartości. Tę metodę powinieneś wykorzystywać w swoich rozwiązaniach i jest ona najlepsza, jeśli rozwiązanie ma być zautomatyzowane.

W dużym skrócie:

  • Tworzymy zmienne T-SQL dla zapytania za pomocą którego uzyskasz listę unikalnych wartości dla nazw kolumn (@BrandNameString ). By wstawić zmienne po przecinku w nawiasach kwadratowych wykorzystaj funkcje MSSQL STRING_AGG() i QUOTENAME(). Funkcja QUOTENAME otacza wartość nawiasem kwadratowym.
  • Tworzymy zmienne T-SQL dla zapytania pivot (@PivotQuery)
  • Implementujemy zmienne w kodzie
  • Za pomocą polecenia EXECUTE sp_executesql odpalamy dynamiczne polecenie tworzące pivot
declare @BrandNameString NVARCHAR(MAX)
declare @PivotQuery NVARCHAR(MAX)
SET @BrandNameString = (SELECT STRING_AGG(quotename(BrandName), ', ') as BrandName FROM (SELECT DISTINCT BrandName FROM Table_1) x)
print @BrandNameString

SET @PivotQuery =
'SELECT ProductionCountry, ' + @BrandNameString + '
FROM
(
select BrandName, ProductionCountry, Quantity from [dbo].[Table_1]
) as SRC
PIVOT
(
sum(Quantity)
FOR BrandName IN (' + @BrandNameString + ')
) as PVT
;'

EXECUTE sp_executesql @PivotQuery

Dynamiczny UnPivot w SQL Server / Transact-SQL

Podobnie jak w przypadku poprzednim, polecenie UnPivot w docelowej formie powinno być wykonywane dynamicznym SQL, by uodpornić skrypt na zmiany wartości kolumn. Polecenie te możemsz wykonać w ten sposób także wtedy, gdy nie znamy nazw kolumn. Przykład jest nieco trudniejszy, gdyż wymaga wyciągnięcia metadanych kolumn z tabel systemowych – w ten sposób wiemy, które kolumny mają typy liczbowe (np bigint, in , float, real) Przykład poniżej:

Jak sprawdzić, w MsSQL, które dane mają typy liczbowe?

SELECT
c.name 'Column Name',t.Name 'Data type'
FROM
sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID('dbo.Table_2') and t.name in ('int', 'bigint', 'float', 'real');

Właściwy kod dla dynamicznego SQL UnPivot:

declare @BrandNameColumn NVARCHAR(MAX)
declare @UnPivotQuery NVARCHAR(MAX)

SET @BrandNameColumn = (
SELECT STRING_AGG(Column_Name, ', ') as BrandName FROM
(
SELECT c.name 'Column_Name' FROM sys.columns c JOIN sys.types t ON
c.user_type_id = t.user_type_id WHERE c.object_id = OBJECT_ID('dbo.Table_2') and t.name in ('int', 'bigint', 'float', 'real')
) x)

SET @UnPivotQuery =
'SELECT ProductionCountry, CarBrand, Qyantity FROM
(
 SELECT ProductionCountry, ' + @BrandNameColumn + ' from [dbo].[Table_2]
) SRC
 UNPIVOT
(Qyantity FOR CarBrand IN
(' + @BrandNameColumn + ')
)AS UPVT
;

'EXECUTE sp_executesql @UnPivotQuery
MsSQL / Transact-SQL – Dynamiczny UnPivot

Źródło: Microsoft

Dzięki za przeczytanie całości. Kurs MSSQL zamieszczam pod tym adresem: Kurs SQL Server. Znajdziesz tam przykłady użycia T-SQL. Kurs jest stale rozwijany.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *