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.

PODOBNE ARTYKUŁYWIĘCEJ OD AUTORA

17 KOMENTARZE

  1. Byłoby fajnie gdybyś przykład zmienił na bardziej ogólny w sensie nazwy kolumn i tabel. Dodatkowo pokazał tabelę z danymi wejściowymi i końcowym rezultatem

      • W SQL akurat czuje się świetnie i cross apply można do tego zaprzęgnąć, ale to zostało dodane do tego by mozna joinować się z funkcjami wywoływanymi dla każdego wiersza.
        Mnie zastanawia czemu użyłeś akurat tego zamiast dedykowanej ku temu składni UNPIVOT, prosty przykład:

        drop table tab
go
CREATE TABLE tab
    ([id] int, [k1] varchar(10), [k2] varchar(10), [k3] varchar(10), [k4] varchar(10), [k5] varchar(10), [k6] varchar(10), [k7] varchar(10), [k8] varchar(10), [k9] varchar(10), [k10] varchar(10))
;
    
INSERT INTO tab
    ([id], [k1], [k2], [k3], [k4], [k5], [k6], [k7], [k8], [k9], [k10])
VALUES
    (1, 'w1', 'w2', 'w3', 'w4', 'w5', 'w6', 'w7', 'w8', 'w9', 'w10'),
    (2, 'w1', 'w2', 'w3', 'w4', 'w5', 'w6', 'w7', 'w8', 'w9', 'w10')
;
select 
	id
	,ColumnName
	,ColumnValue
from	(Select * from tab) p
UNPIVOT (ColumnValue FOR ColumnName in ([k1], [k2], [k3], [k4], [k5], [k6], [k7], [k8], [k9], [k10])) as unpvt

Oczywiście to może wymagać konwersji typów bo unpivot tego wymaga, czyli jeżeli nie pokręciłem to twoje zapytanie można przedstawić w takiej postaci:

select 
	WaterbodyCode
	,ColumnName
	,ColumnValue
from (SELECT
		[T].[WaterbodyCode],
	   ,[W].[WaterbodyTypeName] as [Waterbody Category]
	   ,[W].[OrganisationName] as [Local Authority]
	   ,[W].[Tier1Risk] as 
	FROM
		[TracGroundwaterCharacterisation] AS [T]
		INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
	WHERE 
		[T].[WaterbodyCode] = 'GBNIIE6NB010')
	as pvt
UNPIVOT (ColumnValue FOR ColumnName in ([Waterbody Category], [Local Authority], [WFD Risk])) as unpvt

        Taki sposób wydaje mi się czytelniejszy, bo jak wrócisz do tego i zobaczysz UNPIVOT to od razu wiesz czego się spodziewać, a tak musisz dochodzic jaki cel jest z użycia APPLY

        p.s. da się jakoś formatować kod w komentarzach?

        • Oczywiście zrobiłem błąd, ale tak to jest jak się pisze air code
          linijka:
          ,[W].[Tier1Risk] as
          powinna mieć postać:
          ,[W].[Tier1Risk] as [WFD Risk]

          • Nie próbowalem, bo już sam tsql z apostrofami uruchomił jakieś zabezpieczenia z zaznaczeniem, ze nie jestem robotem

        • da się za pomoca pre tagu.

          i tak, zrobilem z unpivot ale tam byl jeszcze jeden krok ekstra – konwersja danych i to stanowilo problem z Unpivot i spowodowalo ze CROSS byl czytelnijeszy – ale to widze kazdy z nas ma inny poglada na to co jest czytelne a co nie ;) zgadzam sie ze jak widzisz UNPIVOT to juz wiesz o co chodzi.

          No i UNPIVOT bedzie opisany nastepnym razem i na jakie problemy natrafilem i dlaczego bylem zmuszony do zrobienia SELECT w FROM.

          • No to skoro opiszesz UNPIVOT w następnym wpisie to nie będę wyprzedzał, nadmienię tylko że cross apply też będzie miało z tym problem…
            Bazując na takim przykładzie:

            drop table tab
go
CREATE TABLE tab(
	[id] int
	, [k1] int
	, [k2] varchar(10)
	, [k3] varchar(10)
	, [k4] varchar(10)
	, [k5] varchar(10)
	, [k6] varchar(10)
	, [k7] varchar(10)
	, [k8] varchar(10)
	, [k9] varchar(10)
	, [k10] varchar(10)
)
go
    
INSERT INTO tab
    ([id], [k1], [k2], [k3], [k4], [k5], [k6], [k7], [k8], [k9], [k10])
VALUES
    (1, 1, 'w2', 'w3', 'w4', 'w5', 'w6', 'w7', 'w8', 'w9', 'w10'),
    (2, 1, 'w2', 'w3', 'w4', 'w5', 'w6', 'w7', 'w8', 'w9', 'w10')
go

Zwrócę tylko uwagę, że kolumna k1 jest int cała reszta to varchar, to zapytanie z CROSS APPLY:

SELECT
	[T].[ID]
	,[CA].[ColumnName]
	,[CA].[ColumnValue]
FROM
	tab t
CROSS APPLY
(
	VALUES
	('K1', [t].[k1]),
	('K2', [t].[k2]),
	('K3', [t].[k3])
) AS CA([ColumnName], [ColumnValue])

            Wyrzuci błąd:

            Conversion failed when converting the varchar value 'w2' to data type int.

            Więc też trzeba zaznaczyć, że problem może się pojawić bez względu na wynraną metodę.

          • @Michał

            przy intach możliwe, ale to jest dosłownie ten sam przykład i te same dane i dla UNPIVOT musiałem castować a dla cross apply nie. Postarm sie jescze udostepnic zestaw danych – jakis gist stworze tak by kazdy byl na tym samym.

            ale dzieki, tak, przy intach to wszedzie sie wywali, roznica jest gdzie ja moge to castowac a gdzie nie moge.

Comments are closed.