如何将行转换为列?

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