在SQL中透视一个没有聚合的表

Question

我有一个表格，格式如下:

ID    CODE    NAME    VALUE
p1    p       deflect Yes
a1    d       source  Prim
p1    p       source  Dim

我想枢纽有以下内容:

ID    CODE    deflect    source
p1    p       Yes        DIM
a1    d       NULL       Prim

这是我当前的代码:

SELECT *
from
(
select [ID], [CODE], [NAME], [VALUE]
FROM [DATABASE].[dbo].[TABLE]
) SOURCE_TABLE
pivot
(
max(VALUE)
for [NAME] in ('deflect', 'source')
) PIVOT_TABLE;

但是我得到:

Incorrect syntax near 'deflect'.

您将如何编写此pivot代码?

Answer 1

为什么不像下面这样使用条件聚合呢?

select [ID], [CODE], 
max(case when [NAME] = 'deflect' then [VALUE] end) as deflect,
max(case when [NAME] = 'source' then [VALUE] end) as source_
FROM [DATABASE].[dbo].[TABLE]
group by [ID], [CODE]