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

Ostatnim razy wykonaliśmy konwersje kolumn na wiersze z wykorzystaniem funkcji APPLY w T-SQL. Dziś wykonamy to samo, ale wykorzystanym operator – PIVOT i UNPIVOT.

Postaram się jeszcze dzisiaj wrzucić GIST z tabelkami.

PIVOT i UNPIVOT umożliwiają zmienić wyrażenie table-valued na inną tabelę. PIVOT umożliwia zamienić wiersze/wartości na kolumny i wykonuje agregacje na pozostałych kolumnach które są potrzebne w ostatecznym wyniku. UPIVOT zaś wykonuje odwrotną operację – kolumny zamienia na wiersze. Czyli coś co my chcemy osiągnąć. A więc pominę PIVOT i skoncentruje się tutaj na UNPIVOT.

To co jednak jest tutaj BARDZO ważne, to to, że kolumny muszą mieć taki sam typ danych by operacja się powiodła – i to tak dosłownie musi być taki sam (choć polecam przeczytać komentarze pod ostatnim postem). Na przykład NVARCHAR(4000) i NVARCHAR(250) już nie pójdą ze sobą w parze.

W dalszej części trzymam bazowego zapytania z poprzedniego postu:

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

Niestety, ma on pewien problem, to zapytanie ma pewien problem. Mam różne typy i UNPIVOT sobie z tym nie radzi. Dla ułatwienia więc na razie wytniemy kolumnę, która powoduje u mnie problem (Tier1Risk). Więc mamy taki o to wejściowy SELECT:

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

Jeżeli byśmy chcieli to zamienić na ColumnName i ColumnValue to musimy po naszym FROM wykonać operację UNPIVOT. Schemat UNPIVOT ma następujący:

UNPIVOT
(
	NEW_COLUMN_NAME_CONTAINING_VALUE 
	FOR NEW_COLUMN_NAME_CONTAINING_NAME IN (COLUMNS, WHICH, WE, WANT)
) AS UNVPT

A więc stosując tę zasadę i to co zrobiliśmy ostatnio, takie coś powinno nam zadziałać:

SELECT
	[T].[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
UNPIVOT
(
	[ColumnValue] FOR [ColumnName] IN([W].[WaterbodyTypeName], [W].[OrganisationName])
) AS UNPIV

A nie działa. Nie działa z dwóch powodów.

Msg 8156, Level 16, State 1, Line 64
The column 'WaterbodyId' was specified multiple times for 'UNPIV'.
Msg 4104, Level 16, State 1, Line 55
The multi-part identifier "T.WaterbodyCode" could not be bound.

Do pierwszego zaraz wrócimy, drugi to taki, że UNPIVOT ubija to co jest w FROM i zwraca nam to co jest pod UNPIV. A tam nie ma [T].[WaterbodyCode] tylko jest [UNPIV].[WaterbodyCode], zamiana tego na:

SELECT
	[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
	[TracGroundwaterCharacterisation] AS [T]
	INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
UNPIVOT
(
	[ColumnValue] FOR [ColumnName] IN([W].[WaterbodyTypeName], [W].[OrganisationName])
) AS UNPIV

Załatwia jeden problem, ale pozostawia pierwszy spowodowany przez INNER JOIN z tabelą która ma kolumny o tej samej nazwie. W tym WaterbodyId. Więc natrafiamy na problem, że nie możemy zrobić UNPIVOT bo UNPIVOT tworzy sobie tabelę w pamięci i ona już zawiera WaterbodyId.

Możemy to rozwiązać pomijając INNER JOIN jeżeli mamy taką możliwość:

SELECT
	[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
	[Waterbody] AS [W]
UNPIVOT
(
	[ColumnValue] FOR [ColumnName] IN([W].[WaterbodyTypeName], [W].[OrganisationName])
) AS UNPIV

Lub możemy to załatwić SELECT w FROM:

SELECT
	[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
(
	SELECT
		[W].[WaterbodyCode],
		[W].[WaterbodyTypeName],
		[W].[OrganisationName]
	FROM
		[TracGroundwaterCharacterisation] AS [T]
		INNER JOIN [WaterbodyDetailsView] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
) AS T
UNPIVOT
(
	[ColumnValue] FOR [ColumnName] IN([WaterbodyTypeName], [OrganisationName])
) AS UNPIV

I jeżeli już mamy ten SELECT w FROM to też możemy zrobić sobie CAST na odpowiednie typy danych:

SELECT
	[WaterbodyCode],
	[ColumnName],
	[ColumnValue]
FROM
(
	SELECT
		[T].[WaterbodyCode],
		CAST([W].[WaterbodyTypeName] AS NVARCHAR(250)) AS [Waterbody Category],
		CAST([W].[OrganisationName] AS NVARCHAR(250)) AS [Local Authority],
		CAST([W].[Tier1Risk] AS NVARCHAR(250)) AS [WFD Risk]
	FROM
		[TracGroundwaterCharacterisation] AS [T]
		INNER JOIN [Waterbody] AS [W] ON [W].[WaterbodyId] = [T].[WaterbodyId]
) AS [Tab]
UNPIVOT
(
	[ColumnValue] FOR[ ColumnName] IN([Waterbody Category], [Local Authority], [WFD Risk])
) AS UNPIV

I tak o to, mogliśmy przekonwertować wartości na kolumny.

Mimo iż może się wydawać, że UNPIVOT wcale taki fajny nie jest, to, nie. On jest naprawdę fajny i jego głównych zadaniem właśnie jest konwersja kolumn na wiersze. Dla prosty potrzeb albo odpowiednio z preparowanych danych daje nam masę fajnych możliwości. I jeżeli naprawdę wszystko jest po naszej myśli to jest on SUPER zwięzły i czytelny.

Znacie to rozwiązanie? Może znacie jeszcze jeden/inny sposób zamiany kolumn na wiersze?