sql行转列

需求：计算指定部门下所有线别的前三个月的达成率（数据源http://download.csdn.net/download/u010924720/10188327）

方式一

step1:查询每个月线别的达成率

select convert(nvarchar(7),productDate,121) productMonth,company,deptNo,linesNo,lineLevel,
CAST(sum(case reach when 1 then 1 else 0 end)/cast(count(1) as DECIMAL(18,2)) as DECIMAL(6,2)) as reachrace
from whsLineReport where deptNo='TFD30' AND company='AVC2' 
group by convert(nvarchar(7),productDate,121),company,deptNo,linesNo,lineLevel

step2:统计指定日期前三个月的达成率

SELECT company,deptNo,linesNo,lineLevel,
(case productMonth when Convert(varchar(7),dateadd(month,-3,'2017-10-04'),121) then reachrace else 0 end) threerace,
(case productMonth when Convert(varchar(7),dateadd(month,-2,'2017-10-04'),121) then reachrace else 0 end) tworace,
(case productMonth when  Convert(varchar(7),dateadd(month,-1,'2017-10-04'),121) then reachrace else 0 end) onerace
from 
(
select convert(nvarchar(7),productDate,121) productMonth,company,deptNo,linesNo,lineLevel,
CAST(sum(case reach when 1 then 1 else 0 end)/cast(count(1) as DECIMAL(18,2)) as DECIMAL(6,2)) as reachrace
from whsLineReport where deptNo='TFD30' AND company='AVC2' 
group by convert(nvarchar(7),productDate,121),company,deptNo,linesNo,lineLevel
)t

step3:通过行转列统计

select company,deptNo,linesNo,lineLevel,max(threerace) threerace,max(tworace) tworace,max(onerace) onerace from 
(
SELECT company,deptNo,linesNo,lineLevel,
(case productMonth when Convert(varchar(7),dateadd(month,-3,'2017-10-04'),121) then reachrace else 0 end) threerace,
(case productMonth when Convert(varchar(7),dateadd(month,-2,'2017-10-04'),121) then reachrace else 0 end) tworace,
(case productMonth when  Convert(varchar(7),dateadd(month,-1,'2017-10-04'),121) then reachrace else 0 end) onerace
from 
(
select convert(nvarchar(7),productDate,121) productMonth,company,deptNo,linesNo,lineLevel,
CAST(sum(case reach when 1 then 1 else 0 end)/cast(count(1) as DECIMAL(18,2)) as DECIMAL(6,2)) as reachrace
from whsLineReport where deptNo='TFD30' AND company='AVC2' 
group by convert(nvarchar(7),productDate,121),company,deptNo,linesNo,lineLevel
)t

)tb group by company,deptNo,linesNO,lineLevel

方式二

  with t as 
(select o.*,s.stationId+'-'+os.stationName stationId FROM olcPartSetDtl o
      LEFT JOIN olcPartSetDtlStation s on o.partSetNo = s.partSetNo and o.componentPart =s.componentPart 
      LEFT JOIN olcStation os on s.stationId = os.stationId
    where o.partId = 'DFTA0456B2UP145-1' and o.componentPart = 'A103000066' and o.partSetNo = 'd38ef36f-6b33-43b8-8925-79836cd60f66'
)
          
SELECT partId ,componentPart,prodInfo,createTime,createUser,updateTime,updateUser,partSetNo,
stationId = ( STUFF(( SELECT    ',' + stationId
                  FROM      t
                  WHERE     partId = Test.partId and componentPart=Test.componentPart and partSetNo=test.partSetNo
                FOR
                  XML PATH('')
                ), 1, 1, '') )
FROM    t AS Test
GROUP BY partId ,componentPart,prodInfo,createTime,createUser,updateTime,updateUser,partSetNo