我的数据库中有这样的表(sql Server 2008)
ID Type Desc -------------------------------- C-0 Assets No damage C-0 Environment No impact C-0 People No injury or health effect C-0 Reputation No impact C-1 Assets Slight damage C-1 Environment Slight environmental damage C-1 People First Aid Case (FAC) C-1 Reputation Slight impact; Compaints from local community
我必须显示资产,人员,环境和声望作为列,并显示匹配的Desc作为值.但是当我运行透视查询时,我的所有值都为空.
有人可以看我的查询,告诉我我做错了什么？
Select severity_id,pt.[1] As People,[2] as Assets,[3] as Env,[4] as Rep
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([1],[2],[3],[4])
) As pt
这是我的输出
ID People Assets Env Rep ----------------------------------- C-0 NULL NULL NULL NULL C-1 NULL NULL NULL NULL C-2 NULL NULL NULL NULL C-3 NULL NULL NULL NULL C-4 NULL NULL NULL NULL C-5 NULL NULL NULL NULL
解决方法
Select severity_id,pt.People,Assets,Environment,Reputation
FROM
(
select * from COMM.Consequence
) As Temp
PIVOT
(
max([DESCRIPTION])
FOR [TYPE] In([People],[Assets],[Environment],[Reputation])
) As pt
