存储过程（循环语句）

存储过程学到的知识：

1）循环数据查询，利用临时表查询出所要统计的数据

2）行转列。

 

IF Exists(Select * From sysobjects Where Name='SP_Sumcx_product' And xType='P')
  Drop Proc SP_Sumcx_product
go

Create Proc SP_Sumcx_product
  WITH ENCRYPTION
/*
内容：分析促销品数量
时间：2012-05-22
*/
As
Begin

declare @barcode varchar(20),@mom as char(2),@countercode varchar(10)
declare @dxstart varchar(6),@dxend varchar(6),@sql_char1 varchar(3000),@sql_char2 varchar(3000),@sql_char3 varchar(3000)

set @dxstart='120501'
set @dxend='120531'
set @mom=substring(@dxstart,3,2)

   create table #cx_table(regionname varchar(20),work varchar(15),countercode varchar(15),countername varchar(80),htype varchar(20),
mom varchar(4),hday varchar(20),cname varchar(20),cnum int )
 
    create table #pro_table (barcode varchar(15),pro_name varchar(20))

insert into #pro_table
select '6937617069264 ','玫瑰新肌密亮肤水'   union
select '6937617069837','玫瑰活力新肌密5件套'  union
select '6937617066553','柠檬草多效美容液SPF10'  union
select '6937617064245','柠檬草补水遮阳乳60ml'  union
select '6937617069844','玫瑰活力新肌密6件套'  union
select '6943818799058' ,'复古化妆箱' union
select '6933365200201' ,'足浴气血养生机' union
select '6938421612653' ,'名牌空气净化器'

--select * from #pro_table

Declare Cur_Table Cursor For
  Select barcode From #pro_table
 Open Cur_Table
 Fetch Next From Cur_Table Into @barcode
  while @@Fetch_Status = 0
begin
 set @sql_char1='select b1.regionname as ''大区'', a1.countercode as ''代理商'', a1.countercode  as ''专柜编码''
     ,b1.countername as ''专柜名称'',''重点中促'' as  ''活动类型 (大促、重点中促、中促）'','+@mom+' as ''月份'',
     '''+@dxstart+'-'+@dxend+''' as ''活动时间'', '

 set @sql_char1=@sql_char1+''''+@barcode+''' as ''名称'',sum(quantity) as ''数量'' from transactionlog a1 ,counters as b1
     where  a1.countercode in (select  distinct a.countercode  from transactionlog as a ,counters as b
     where a.countercode=b.countercode and  barcode='''+@barcode+''' and  txddate between '''+@dxstart+'''
     and '''+@dxend+'''  )  and a1.countercode=b1.countercode  and b1.regionname in (''华北区'',''华东区'',''华南区'' )
                    and txddate between '''+@dxstart+''' and
     '''+@dxend+'''  and barcode ='''+@barcode+''' and txdtype=''NS''  group by a1.countercode,b1.countername
     ,b1.regionname'
 set  @sql_char1 ='insert into #cx_table '+@sql_char1

exec (@sql_char1)
 Fetch Next From Cur_Table Into @barcode
end
Close Cur_Table
Deallocate Cur_Table

--select *  from #cx_table where countercode='01037105' and cname='6937617069837'
--truncate table #cx_table
--truncate table #pro_table
--select * from #cx_table
--行转列

select  regionname as '大区', countercode as '代理商', countercode  as '专柜编码'
,countername as '专柜名称',htype as  '活动类型 (大促、重点中促、中促）',mom as '月份',
hday as '活动时间',max(case when cname ='6937617069264 ' then cnum else 0 end) '玫瑰新肌密亮肤水'  ,
max(case when cname ='6937617069837' then cnum else 0 end) '玫瑰活力新肌密5件套' ,
max(case when cname ='6937617066553' then cnum else 0 end) '柠檬草多效美容液SPF1' ,
max(case when cname ='6937617064245' then cnum else 0 end) '柠檬草补水遮阳乳60ml' ,
max(case when cname ='6937617069844' then cnum else 0 end) '玫瑰活力新肌密6件套' ,
max(case when cname ='6943818799058' then cnum else 0 end) '复古化妆箱' ,
max(case when cname ='6933365200201' then cnum else 0 end) '足浴气血养生机' ,
max(case when cname ='6938421612653' then cnum else 0 end) '名牌空气净化器'
  from #cx_table group by regionname,countercode,countercode,countername,htype,mom,hday

Drop Table #pro_table
Drop Table #cx_table
End  --EndProc
go
--select * from #pro_table
--select * from #cx_table
-- exec  SP_Sumcx_product