在SQL Server Management Studio中将行转换为列

I'm posting the query below which I used to retrieve the data and output how it shows and how do I need also .. please let me know how can I convert rows to columns data

我发布下面的查询,我用它来检索数据并输出它显示的方式以及我还需要..请让我知道如何将行转换为列数据

      [1]: http://www.itdaan.com/imgs/8/8/6/6/53/cfa48798fe50fbb72afb28a0a8d7aef7.jpe

And output I need it to be as below.

输出我需要它如下。

       AME  AMV BHV BRV EOR IPA IPB LTC OHW
00      3    3   3    1   1  3   3   1   1
01     38   38  38  217 229

2 个解决方案

#1

-1

Just use conditional aggregation since the columns are constant.

只需使用条件聚合,因为列是常量。

select
    BarCdmID
    , AME = MAX(case when Facility_MisFacID = 'AME' then MyCount end) 
    , AMV = MAX(case when Facility_MisFacID = 'AMV' then MyCount end) 
    , BHV = MAX(case when Facility_MisFacID = 'BHV' then MyCount end) 
    , BRV = MAX(case when Facility_MisFacID = 'BRV' then MyCount end) 
    , EOR = MAX(case when Facility_MisFacID = 'EOR' then MyCount end) 
    , IPA = MAX(case when Facility_MisFacID = 'IPA' then MyCount end) 
    , IPB = MAX(case when Facility_MisFacID = 'IPB' then MyCount end) 
    , LTC = MAX(case when Facility_MisFacID = 'LTC' then MyCount end) 
    , OHW = MAX(case when Facility_MisFacID = 'OHW' then MyCount end) 
from
(
    Select BarCdmID = LEFT(BarCdmID, 2)
        , Facility_MisFacID
        , MyCount = count(*)
    from BarCdm_Facil
    group by LEFT(BarCdmID, 2)
        , Facility_MisFacID

) x
group by X.BarCdmID
order by BarCdmID

#2

you should use pivot when you want to convert rows to column base on one column

如果要将行转换为一列上的列,则应使用pivot

    ( select left(BarCdmID,2) as BarCdmID ,
[0] as AME,
[1] as AMV, 
[2] as BHV ,
[3] as BRV,
[4] as EOR ,
[5] as IPA,
[6] as IPB,
[7] as LTC,
[8] as OHW from BARCDM_FACIL) T
    pivot 
(facility_misfacID
 FOR T.BarCdmID 
IN [0],[1],[2],[3],[4],[5],[6],[7],[8]
    ) as pvt

#1

-1