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
0
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
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
0
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