SQL Server i PIVOT UNPIVOT

lechert

<right>Łukasz Lechert</right>

SQL Server udostępnia programiście rozszerzenia polecenia SELECT. Są nimi PIVOT i UNPIVOT. Rozszerzenie PIVOT pozwala na wygodne formatowanie wierszy tabeli oraz prezentacje przekształceń w kolumnach. UNPIVOT jest rozszerzeniem o odwrotnym działaniu oraz przekształca kolumny w wiersze.

Dane

CREATE TABLE Artykul
( 
  id    int, 
  Nazwa varchar(30),
  Region tinyint,
  Cena  decimal(10,2)

)

INSERT INTO Artykul VALUES
(
 1, 'BMW Z3', 1, 9000.99
)


INSERT INTO Artykul VALUES
(
 2, 'BMW Z4', 1, 19000.99
)

INSERT INTO Artykul VALUES
(
 3, 'Audi A4', 2, 11000.99
)

INSERT INTO Artykul VALUES
(
 4, 'Audi A6', 2, 15000.99
)
INSERT INTO Artykul VALUES
(
 5, 'Mercedes A160', 3, 10000.99
)
INSERT INTO Artykul VALUES
(
 6, 'Mercedes CLK', 3, 18000.00
)
INSERT INTO Artykul VALUES
(
 7, 'Fiat Panda', 3, 7600.99
)
INSERT INTO Artykul VALUES
(
 8, 'Audi Quattro', 1, 8200.00
)
INSERT INTO Artykul VALUES
(
 9, 'Golf 5', 3, 8900.99
)
INSERT INTO Artykul VALUES
(
 10, 'Ford Focus', 1, 10000.99
)

Rozwiązanie dla SQL Server 2000

SELECT 
SUM(CASE
WHEN Region = 1 THEN
 Cena
ELSE
 0
END) AS [1],
SUM(CASE
WHEN Region = 2 THEN
 Cena
ELSE
 0
END) AS [2],
SUM(CASE
WHEN Region = 3 THEN
 Cena
ELSE
 0
END) AS [3]
FROM Artykul;

-- Sumy
-- Region 1=46202,97 Region 2=26001,98 Region 3=44502,97

PIVOT od wersji SQL Server 2005 przekształcanie wierszy w kolumny (sumy)

SELECT 'Suma cen z oferty poszczegolnych regionow' AS RegionOferta, 
[1] AS Region1, [2] AS Region2, [3] AS Region3
FROM
(SELECT Region, Cena FROM dbo.Artykul) AS TabelaArtykulow
 PIVOT
 ( 
  SUM(Cena) 
  FOR Region IN ([1],[2],[3]) 
 ) AS PivotCen

UNPIVOT - operacja odwrotna

CREATE TABLE Ceny 
( 
  id       tinyint,
  Region1  decimal(10,2),
  Region2  decimal(10,2),
  Region3  decimal(10,2)

)

-- Dane

INSERT INTO Ceny VALUES
(
 1, 46202.97, 26001.98, 44502.97
)

SELECT Region, Suma
FROM
(SELECT Region1, Region2, Region3 from dbo.Ceny) AS TabelaCen
UNPIVOT
(
 Suma
 FOR Region IN (Region1, Region2, Region3)
) AS UnpivotCen

0 komentarzy