sql 动态行转列

sql 动态行转列

DECLARE @smallGongDuan VARCHAR(50), @date DATETIME,@endDate DATETIME,@bc VARCHAR(200),@baiWan VARCHAR(20),@bcName VARCHAR(20)
--@gongDuan='外观挑选,分级补电,制浆,铝塑膜,极耳,隔膜纸,组装,二次分级,分级,化成,铝转镍,抽气折边,自动封装,自动焊接,点焊封边,二次容检,入库前容检,制片,注液,卷绕,机叠,叠片,涂布',
SET @smallGongDuan='制浆'
set @date ='2016-06-21'
set @endDate ='2016-06-23'
set @bc ='''D2'',''D1'',''B1'',''A1'',''F'',''G'',''D'',''B'',''E'',''C'',''A'''
set @baiWan =''
set @bcName =''

if(@smallGongDuan='制浆')
begin
--------查询制浆在该日期段的最大版本
select * into #zhijiangBills
from zhiJiangDianBill a
where a.version = (
select max(version)
from zhiJiangDianBill b where
a.planDate=b.planDate and b.planDate BETWEEN @date and @endDate
)

declare @zjBC varchar(100)

if @bc is null or @bc=''      
   set @zjBC='A,B,C,D,E,F,G,A1,B1'      

else
begin
set @zjBC=replace(@bc,'''','')
end

SELECT [zJHao],tp.orderName,sum([taskNum]) as taskNum,sum([zhiJiangNum]) as zhiJiangNum,[jiCode],[jiXing],[startDate],[gongYiDate],[endDate]      

,[bc],[baiWan],[firstOrder],z.[planDate],[version],z.[fName],[tuBuJiCode]
into #zhijiangBill FROM #zhijiangBills z
left join tblProSchedu tp on z.shengChanHao=tp.shengChanHao
where z.bc IN (select * from dbo.f_split(@zjBC,',')) and baiWan like '%'+@baiWan+'%'
group by [zJHao],[jiCode],[jiXing],[startDate],[gongYiDate],[endDate],[bc],[baiWan],[firstOrder],z.[planDate],[version],z.[fName],[tuBuJiCode],tp.orderName

SELECT * INTO #zhijian FROM (

select 0 as did,a.zjhao as shengChanHao,a.orderName as model,convert(varchar(20),taskNum) as planNum ,
a.jiXing ,zhiJiangNum as taskNum ,convert(int,0) taskPianNum ,convert(float,0) taskMiNum ,'' upGui,'' downGui,
a.bc ,a.baiWan, convert(varchar(5),a.firstOrder) as firstOrder,a.version,a.planDate,'制浆' as gongDuan ,a.fname,
convert(float,0) unitNum ,'' xiaoPian,'' gongYi,
convert(datetime,null) as startTime,convert(datetime,null) as endTime,'' as tuBujiTai,'' as proCode,'' as znGongYiCode,'' as Remark,
convert(float,0) as lvSuMoNum,convert(float,0) as geMoZhiNum,'' as dianJieGuiGe,
convert(int,isnull(b.endNum,0))/1000 as endNum,0 as upEndNum,0 as downEndNum,b.endDate,'1900-01-01' as downEndDate,bc.name as bcName,
case when b.endDate<=dateadd(hour,1,a.endDate) then 'OK' ELSE 'NG' end as endState
,'' as downEndState
from #zhijiangBill a
left join
(
select substring(zjHao,1,7) as zjhao,banCi as bc,ji as jixing,max(okDate) as endDate,outDate as sDate,sum(outNum) as endNum
from zhijiang
--where outDate between @date and @endDate
group by substring(zjHao,1,7),banCi,ji,okDate,outDate
)b
on a.zjhao =b.zjHao and a.jixing=b.jixing
--inner join tblProSchedu tp on a.shengChanHao=tp.shengChanHao
left join banCi bc on a.bc=bc.classCode and a.fname=bc.fname and bc.gong='制浆'

) zhijian

--SELECT * FROM #zhijian

SELECT gongDuan,planDate,COUNT(1) AS JiHuaPici INTO #ZhiJianJiHuaPiCi FROM #zhijian WHERE planDate IS NOT NULL GROUP BY gongDuan,planDate
SELECT gongDuan,planDate,COUNT(1) AS WangChengPici INTO #ZhiJianWanChengPiCi FROM #zhijian WHERE planDate IS NOT NULL AND endState='OK' GROUP BY gongDuan,planDate
SELECT gongDuan,planDate,COUNT(1) AS WeiWanChengPici INTO #ZhiJianWeiWanChengPiCi FROM #zhijian WHERE planDate IS NOT NULL AND endState='NG' GROUP BY gongDuan,planDate

SELECT jhpc.*,wcpc.WangChengPici,wwcpc.WeiWanChengPici INTO #ZhiJianPivot FROM #ZhiJianJiHuaPiCi jhpc
LEFT JOIN #ZhiJianWanChengPiCi wcpc ON jhpc.gongDuan = wcpc.gongDuan AND jhpc.planDate = wcpc.planDate
LEFT JOIN #ZhiJianWeiWanChengPiCi wwcpc ON wcpc.gongDuan = wwcpc.gongDuan AND wcpc.planDate = wwcpc.planDate
ORDER BY jhpc.planDate

--SELECT * FROM #ZhiJianPivot

------游标遍历时间用作列
declare auth_cur cursor FOR SELECT planDate FROM #ZhiJianPivot
DECLARE @timeS DATETIME, @timeSlist VARCHAR(2000)
SET @timeS=NULL
SET @timeSlist=''
open auth_cur

fetch next from auth_cur into @timeS

while (@@fetch_status=0)

BEGIN

--PRINT '['+convert(varchar(10),@timeS,120)+']'+',' 
SET @timeSlist=@timeSlist+'['+CONVERT(varchar(100),convert(varchar(10),@timeS,120), 23)+']'+',' 
fetch next from auth_cur into @timeS 

END

close auth_cur

deallocate auth_cur
SET @timeSlist=SUBSTRING(@timeSlist,0,LEN(@timeSlist))
--SELECT @timeSlist
--------游标遍历时间用作列

DECLARE @sqlStr NVARCHAR(MAX)
SET @sqlStr=''
SET @sqlStr=@sqlStr+'SELECT ''计划数''AS ''日期'',FROM (select gongduan,planDate,JiHuaPici from #ZhiJianPivot)cc PIVOT(sum(JiHuaPici) FOR planDate IN('+@timeSlist+')) planDate UNION ALL '
SET @sqlStr=@sqlStr+'SELECT ''完成数''AS ''日期'',FROM (select gongduan,planDate,WangChengPici from #ZhiJianPivot)cc PIVOT(sum(WangChengPici) FOR planDate IN('+@timeSlist+')) planDate UNION ALL '
SET @sqlStr=@sqlStr+'SELECT ''未完成数''AS ''日期'',*FROM (select gongduan,planDate,WeiWanChengPici from #ZhiJianPivot)cc PIVOT(sum(WeiWanChengPici) FOR planDate IN('+@timeSlist+')) planDate '

EXEC (@sqlStr)

declare @sql varchar(max)
--SET @sql='SELECT ''计划数''AS ''日期'''
--SET @sql=@sql+',sum(case when planDate='+convert(varchar(10),planDate,120)+'then JiHuaPici else 0 end) as '+'['+convert(varchar(10),planDate,120)+']' from #ZhiJianPivot

--set @sql='SELECT gongDuan,WangChengPici,WangChengPici,WeiWanChengPici '
--select @sql=@sql+',sum(case when planDate='+convert(varchar(10),planDate,120)+'then JiHuaPici else 0 end) as '+'['+convert(varchar(10),planDate,120)+']' from #ZhiJianPivot
--SELECT @sql

--exec (@sql+'from #ZhiJianPivot'+' GROUP BY gongDuan,WangChengPici,WangChengPici,WeiWanChengPici,planDate')
--SELECT gongDuan,WangChengPici,WangChengPici,WeiWanChengPici ,
--sum(case when planDate=2016-06-21then JiHuaPici else 0 end) as [2016-06-21],
--sum(case when planDate=2016-06-22then JiHuaPici else 0 end) as [2016-06-22],
--sum(case when planDate=2016-06-23then JiHuaPici else 0 end) as [2016-06-23]

drop table #zhijiangBill,#zhijiangBills,#zhijian,#ZhiJianJiHuaPiCi,#ZhiJianWanChengPiCi,#ZhiJianWeiWanChengPiCi,#ZhiJianPivot

end

posted on 2016-06-27 09:59 影子博客 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/VictorBlog/p/5619312.html