UNPIVOT()

PIVOT() a UNPIVOT() jsou velmi užitečné funkce jazyka T-SQL. My si dnes ukážeme použití UNPIVOT() funkce na příkladu s objednávkami a jejich cenou a daňovou sazbou. K tomu přidáme i ukázku toho, jak využít možnosti řetězení UNPIVOT() funkcí v jedno dotazu a jak šikovným trikem dosáhnout požadovaného výstupu. A jako bonus si přidáme SUM() s OVER().

Nejprve si vytvoříme zkušební tabulku a naplníme ji daty:

CREATE TABLE [dbo].[SampleTable] (
	[Order_ID] INT NOT NULL PRIMARY KEY,
	[Amount1] MONEY NOT NULL DEFAULT(0),
	[VAT1] MONEY NOT NULL DEFAULT(0),
	[Amount2] MONEY NOT NULL DEFAULT(0),
	[VAT2] MONEY NOT NULL DEFAULT(0),
	[Amount3] MONEY NOT NULL DEFAULT(0),
	[VAT3] MONEY NOT NULL DEFAULT(0)
)
GO

INSERT INTO [dbo].[SampleTable] 
	( [Order_ID], [Amount1], [VAT1], [Amount2], [VAT2], [Amount3], [VAT3] )
	VALUES ( 1, 100, 5, 200, 21, 300, 10),
		   ( 2, 100, 5, 0, 0, 300, 10),
		   ( 3, 500, 5, 600, 7, 700, 10)
GO

SELECT * FROM [dbo].[SampleTable]
GO

V tabulce máme tři objednávky a pro každou z nich máme tři různé částky a tři různé sazby. Aplikace nám to ukládá takto poněkud nešťastně do řádku a my chceme data dostat do podoby, kdy s nimi budeme moci pracovat jako s klasickým datovým setem a počítat celkové částky za objednávky nebo celou jejich množinu.

S pomocí UNPIVOT() můžeme v nejjednodušší možné variantě otočit jednoduše částky ze sloupců do řádků:

SELECT [Order_ID], [Amount], [Amount_Value]
FROM [dbo].[SampleTable]  
	UNPIVOT ([Amount_Value] FOR [Amount] IN ([Amount1], [Amount2], [Amount3])) AS [Amount_Value]  
GO

Nyní vidíme, že se nám každá objednávka objevila třikrát a jednotlivé částky máme místo na řádcí ve sloupcích. Stejným způsobem bychom rádi otočili i hdnoty VAT. Toho může dosáhnout buď pomocí vnořených dotazů, kdy si uděláme stejný dotaz se sloupci pro VAT a připojíme ho joinem přes Order_ID k prvnímu dotazu. Ale může to zkusit i vícenásobným použitím UNPIVOT() funkce v rámci jedno dotazu:

SELECT [Order_ID], [Amount], [Amount_Value], [VAT], [VAT_Value] 
FROM [dbo].[SampleTable]  
	UNPIVOT ([Amount_Value] FOR [Amount] IN ([Amount1], [Amount2], [Amount3])) AS [Amount_Value]  
	UNPIVOT ([VAT_Value]	FOR [VAT] IN ([VAT1], [VAT2], [VAT3])) AS [VAT_Value]
GO

To ale není úplně výsledek, který bychom si přáli. Mezi UNPIVOT() funkcemi totiž není žádná vazba a došlo tak ke kartézkému součinu jejich množin. Můžeme vše ale velmi jednoduše napravit s použitím chytrého filtru ve WHERE:

SELECT [Order_ID], [Amount], [Amount_Value], [VAT], [VAT_Value] 
FROM [dbo].[SampleTable]  
	UNPIVOT ([Amount_Value] FOR [Amount] IN ([Amount1], [Amount2], [Amount3])) AS [Amount_Value]  
	UNPIVOT ([VAT_Value]	FOR [VAT] IN ([VAT1], [VAT2], [VAT3])) AS [VAT_Value]
WHERE RIGHT([Amount], 1) = RIGHT([VAT], 1)
GO

To už je výsledek, který nám udělá radost. Ve WHERE jsme k sobě podle posledního znaku (čísla v názvu sloupce) jednoduše připojili správné Amount a VAT. Je to trošku trik, který nám funguje, protože jsme na chování UNPIVOT() funkce při jejím vícenásobném použití mysleli již při návrhu tabulky.

A když už máme data takto pěkně otočené z řádků do sloupců, můžeme si pěkně spočítat nějaké ty celkové částky:

SELECT 
	*,
	SUM([Amount_Value]) OVER (PARTITION BY [ord].[Order_ID]) [Sum_AMT_Order],
	SUM([Amount_Value]) OVER () [Sum_AMT_Total]
FROM (
		SELECT [Order_ID], [Amount], [Amount_Value], [VAT], [VAT_Value] 
		FROM [dbo].[SampleTable]  
			UNPIVOT ([Amount_Value] FOR [Amount] IN ([Amount1], [Amount2], [Amount3])) AS [Amount_Value]  
			UNPIVOT ([VAT_Value]	FOR [VAT] IN ([VAT1], [VAT2], [VAT3])) AS [VAT_Value]
		WHERE RIGHT([Amount], 1) = RIGHT([VAT], 1)
	) [ord]
GO

Více o použítí UNPIVOT() funkce najde v MSDN.