Zamiana kolumn na wiersze – T-SQL (Rozwiązanie 1)

Ostatnio mam dziwne sytuacje związane z kodem i SQL – na przykład zamiana prostej tabelki na nieskończenie zagnieżdżone drzewo. Albo zwróć mi wszystkie dane, ale chcę je w postaci dwóch kolumn: ColumnName i ColumnValue. Czy to są dziwne rozwiązania czy też nie, to pewnie zależy od specyfiki naszej pracy. Ja mam z takimi zapytaniami rzadki kontakt ale jak już mam, to staram się to zawsze udokumentować – dla siebie. By zapamiętać.

Tak też było w tym przypadku. Zapytanie zawracające paręnaście wierszy danych i około 10 kolumn miałem zamienić na trzy kolumny: Identifier, ColumnName i ColumnValue. Dla zachowania prostoty ograniczę się w przykładzie do mniejszej liczby kolumn w domyślnym zapytaniu.

Więc jakie mamy opcje przekonwertowania danych? Jedną z nich jest opcja APPLY, która wykonuje funkcję dla każdego wiersza danych. Dokładnie mówiąc wykonuje ona funkcję na każdym wierszu z zapytania nadrzędnego. Zaraz sobie powiemy co to jest zapytanie nadrzędne. Możemy wyróżnić dwie funkcje APPLY – CROSS APPLY i OUTER APPLY. Różnica jest taka, że CROSS zwróci nam jedynie wiersze z nadrzędnego zapytania które zostały zwrócone przez funkcje table-valued. Przy OUTER za to, zostanie zwrócone wszystko. Nawet jeżeli coś nie zostało przekazane lub obliczone przez funkcję table-valued.

Warto powiedzieć, że wynikiem są kolumny zwrócone przez zapytanie nadrzędne i kolumny dodane przez APPLY.

Zanim przejdziemy dalej, zapytanie nadrzędne to, zapytanie SELECT które wykonujemy tuż przed wywołaniem CROSS|OUTER APPLY. Weźmy prosty przykład ode mnie z pracy:

SELECT
	[T].[WaterbodyCode],
	[W].[WaterbodyTypeName],
	[W].[OrganisationName],
	[W].[Tier1Risk]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]

To jest bazowe zapytanie, które zwraca nam jakieś tam dane. My chcemy to zamienić na mniej więcej taką postać:

SELECT
	[T].[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]

Gdzie ColumnName i ColumnValue będą odpowiadały nazwie kolumny dla WaterbodyTypeName i jej wartości. I tak dal pozostałych kolumn.

Na razie załóżmy, że nie mamy kolumn ColumnName i ColumnValue więc nasze zapytanie wygląda tak:

SELECT
	[T].[WaterbodyCode]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]

Na tym zapytaniu będziemy wywoływać funkcję CROSS APPLY.

SELECT
	[T].[WaterbodyCode]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
CROSS APPLY
(
) AS CA

Kod powyżej nie zadziała! Ale to jest krok który chciałem pokazać. Teraz naszym zapytaniem nadrzędnym jest SELECT..FROM i wszystkie rekordy z niego zostaną podane funkcji table-valued.

By uzyskać wynik, który nas interesuje, możemy założyć, że CROSS APPLY będzie zwracało nam dwie kolumny – ColumnName i ColumnValue, które będziemy chcieli wyświetlić:

SELECT
	[T].[WaterbodyCode],
	[CA].[ColumnName],
	[CA].[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
CROSS APPLY
(
) AS CA([ColumnName], [ColumnValue])

To jest zrozumiałe na razie? Wszystkie wiersze z FROM zostaną poddane funkcji CROSS APPLY która zwróci nam dwie ekstra kolumny. Teraz w CROSS APPLY możemy wybrać wiersze do naszej funkcji zwracającej tabele:

SELECT
	[T].[WaterbodyCode],
	[CA].[ColumnName],
	[CA].[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
CROSS APPLY
(
	SELECT 'Waterbody Category', [W].[WaterbodyTypeName] UNION ALL
	SELECT 'Local Authority', [W].[OrganisationName] UNION ALL
	SELECT 'WFD Risk', [W].[Tier1Risk]
) AS CA([ColumnName], [ColumnValue])

Lub za pomocą VALUES (szybciej, więcej informacji na temat opcji VALUES, oraz dokumentacja):

SELECT
	[T].[WaterbodyCode],
	[CA].[ColumnName],
	[CA].[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
CROSS APPLY
(
	VALUES
	('Waterbody Category', [W].[WaterbodyTypeName]),
	('Local Authority', [W].[OrganisationName]),
	('WFD Risk', [W].[Tier1Risk])
) AS CA([ColumnName], [ColumnValue])

I tak o to uzyskamy wynik:

WaterbodyCode       ColumnName          ColumnValue
GBNIIE6NB010        Waterbody Category  Coastal
GBNIIE6NB010        Local Authority     N.A.
GBNIIE6NB010        WFD Risk            Review

Do tego wszystkiego, możemy zastosować WHERE na końcu (po wykonaniu CROSS APPLY):

SELECT
	[T].[WaterbodyCode],
	[CA].[ColumnName],
	[CA].[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
CROSS APPLY
(
	VALUES
	('Waterbody Category', [W].[WaterbodyTypeName]),
	('Local Authority', [W].[OrganisationName]),
	('WFD Risk', [W].[Tier1Risk])
) AS CA([ColumnName], [ColumnValue])
WHERE 
	[T].[WaterbodyCode] = 'GBNIIE6NB010'

Bardzo fajne i przyjemnie. Jedyny problem jaki ja miałem to zrozumienie tego nadrzędnego SELECT i tego, że APPLY zwraca mi tabelę. Jak to opanowałem to już zapytania szły mi znacznie lepiej.

Ale jak w we wszystkim, to nie jest jedyna opcja jaką możemy zrobić/wykorzystać. Na innym sposobem skoncentruje się następnym razem.