如何将行转换为列?

问题描述

我有一张这样的桌子:

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