如何在 SQL Server 中旋转文本列? [英] How to pivot text columns in SQL Server?

问题描述

我的数据库中有一个这样的表 (SQL Server 2008)

I have a table like this in my database (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

我必须将 Assets、People、Environment 和 Reputation 显示为列，并将匹配的 Desc 显示为值.但是当我运行数据透视查询时，我所有的值都是空的.

i have to display the Assets, People, Environment and Reputation as columns and display matched Desc as values. But when i run the pivot query, all my values are null.

有人可以查看我的查询并告诉我哪里做错了吗?

Can somebody look into my query ans tell me where i am doing wrong?

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

这篇关于如何在 SQL Server 中旋转文本列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！