Dane te można przekształcić na kilka sposobów. W swoim oryginalnym poście stwierdziłeś, że PIVOTwydaje się to zbyt skomplikowane dla tego scenariusza, ale można go bardzo łatwo zastosować za pomocą funkcji UNPIVOTiPIVOT w SQL Server.
Jeśli jednak nie masz dostępu do tych funkcji, można to zreplikować za pomocą UNION ALLdo, UNPIVOTa następnie funkcji agregującej z CASEinstrukcją do PIVOT:
Utwórz tabelę:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Wersja Union All, Aggregate i CASE:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
Zobacz SQL Fiddle with Demo
Do UNION ALLwykonuje UNPIVOTdanych poprzez przekształcenie kolumny Paul, John, Tim, Ericw osobnych wierszach. Następnie stosujesz funkcję agregującą sum()z caseinstrukcją, aby uzyskać nowe kolumny dla każdej z nich color.
Wersja statyczna Unpivot i Pivot:
Zarówno UNPIVOTi PIVOTfunkcje w serwerze SQL dokonać tej przemiany jest znacznie łatwiejsze. Jeśli znasz wszystkie wartości, które chcesz przekształcić, możesz zakodować je na stałe do wersji statycznej, aby uzyskać wynik:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
Zobacz SQL Fiddle with Demo
Zapytanie wewnętrzne z UNPIVOToperatorem spełnia tę samą funkcję co UNION ALL. Pobiera listę kolumn i zamienia ją na wiersze, a PIVOTnastępnie wykonuje ostateczną transformację w kolumny.
Wersja Dynamic Pivot:
Jeśli masz nieznaną liczbę kolumn ( Paul, John, Tim, Ericw twoim przykładzie), a następnie nieznaną liczbę kolorów do przekształcenia, możesz użyć dynamicznego sql do wygenerowania listy, UNPIVOTa następnie PIVOT:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
Zobacz SQL Fiddle with Demo
Wersja dynamiczna wysyła zapytanie zarówno yourtabledo sys.columnstabeli, jak i do tabeli, aby wygenerować listę elementów do UNPIVOTi PIVOT. To jest następnie dodawane do ciągu zapytania do wykonania. Zaletą wersji dynamicznej jest to, że masz listę zmian colorsi / lub namesspowoduje to wygenerowanie listy w czasie wykonywania.
Wszystkie trzy zapytania dadzą ten sam wynik:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |