问题描述
我有一张这样的桌子:
Name Value --------------- toyota yellow nissan blue toyota red nissan black
我想把表格转换成这样:
I want to convert the table to this:
toyota nissan ---------------- yellow blue red black
我该怎么做?
我尝试使用这个命令:
SELECT * (CASE Name WHEN 'toyota' THEN Value END) toyota, (CASE Name WHEN 'nissan' THEN Value END) nissan FROM testTable
但结果是这样的表格:
toyota nissan ---------------------- yellow NULL NULL blue red NULL NULL black
帮助?谢谢
推荐答案
使用 pivot 也是可能的:
declare @table table (Name varchar(50), Value varchar(50)) insert into @table values ('toyota', 'yellow'), ('nissan', 'blue'), ('toyota', 'red'), ('nissan', 'black') ;with t as ( select *, rn = row_number() over (partition by Name order by Value) from @table ) select Toyota, Nissan from t pivot (max(Value) for Name in ([Toyota],[Nissan])) p