我正在尝试根据以下数据转换列中的行：
Select EvolutionOrder, Age from Product where ProductID = 1
EvolutionOrder Age
-------------- ----
1 0012
2 1324
3 2536
4 3700
Select EvolutionOrder, Age from Product where ProductID = 2
EvolutionOrder Age
-------------- ----
1 QUEE
1 HIVE
我需要结果为：
Product Age1 Age2 Age3 Age4 Age5
------- ---- ---- ---- ---- ----
1 0012 1324 2536 3700 NULL
2 QUEE HIVE NULL NULL NULL
我尝试使用PIVOT，但由于年龄列的汇总函数随值消失而无法使用
SELECT ProductID,
[1] AS Age1,
[2] AS Age2,
[3] AS age3,
[4] AS age4
FROM Product
PIVOT (MAX(Age) FOR EvolutionOrder IN ([1], [2], [3], [4])) piv;
我知道：
ProductID Age1 Age2 age3 age4
--------- ---- ---- ---- ----
1 0012 1324 2536 3700
2 QUEE NULL NULL NULL --> HIVE Vanished
我得到的另一种方式是运行下面的查询：
with vFX1 as (Select Age Age1 from Product
where ProductID = 1 and EvolutionOrder = 1)
, vFX2 as (Select Age Age2 from Product
where ProductID = 1 and EvolutionOrder = 2)
, vFX3 as (Select Age Age3 from Product
where ProductID = 1 and EvolutionOrder = 3)
, vFX4 as (Select Age Age4 from Product
where ProductID = 1 and EvolutionOrder = 4)
, vFX5 as (Select Age Age5 from Product
where ProductID = 1 and EvolutionOrder = 5)
select * from vFX1 Left outer Join vFX2 on 1=1
Left outer Join vFX3 on 1=1
Left outer Join vFX4 on 1=1
Left outer Join vFX5 on 1=1
我不喜欢这种解决方案，因为我需要多次运行同一张表并消失数据。
我如何只用一个ANSI sql最聪明的查询就能做到这一点？
在脚本下面创建数据：
Create table Product
(ProductID Int
,EvolutionOrder Int
,Age Char(4))
Insert into Product values (1,1,'0012');
Insert into Product values (1,2,'1324');
Insert into Product values (1,3,'2536');
Insert into Product values (1,4,'3700');
Insert into Product values (2,1,'QUEE');
Insert into Product values (2,1,'HIVE');
谢谢
注意：由于获得帮助而进行了更新
最佳答案
语法略有不同，请看一下：
Select [1] Age1, [2] Age2, [3] Age3, [4] Age4, [5] Age5
from Product
PIVOT (MAX(Age) for EvolutionOrder in ([1],[2],[3],[4],[5])) P
不要害怕骨料。如果只有一个值，则MIN或MAX将等于该值。