要求导出的excel 每一列显示的各个分公司的名称,每一行显示的各个分公司的产品数量,且每一次都要导出5版数据。
显示样例:
数据表结构
所以这里需要动态的行转列,动态是因为列分公司是动态的,会进行新增和删除。所以这里会拼接sql
case when ... else ... end动态拼接
比如:
WHILE EXISTS(SELECT code FROM @Cus)
BEGIN
-- 也可以使用top 1
SET ROWCOUNT 1
SELECT @tempcode= code, @tempname= name FROM @Cus;
set @sqlstr1= @sqlstr1 + 'sum(['+@tempname+']) as ['+@tempname+'],';
set @sqlstr2=@sqlstr2 + 'case CustomerCode when '''+@tempcode +''' then sum(qty) else 0 end as '''+@tempname+''',';
SET ROWCOUNT 0
DELETE FROM @Cus WHERE code=@tempcode;
END
全部执行的存储过程:
1 DECLARE @Cus TABLE
2 (
3 code [NVARCHAR](50),
4 name [NVARCHAR](200)
5 );
6
7 insert into @Cus select code,name from Bus_Base_CustomerCode
8 DECLARE @selectMonth INT
9 declare @tempcode varchar(max)
10 declare @tempname varchar(max)
11 declare @sqlstr1 varchar(max)
12 declare @sqlstr2 varchar(max)
13 declare @sqlstrall varchar(max)
14 DECLARE @sqlstrall2 VARCHAR(max)
15 DECLARE @sqlstrall3 VARCHAR(max)
16 DECLARE @sqlstrall4 VARCHAR(max)
17 DECLARE @sqlstrall5 VARCHAR(max)
18 set @sqlstr1=''
19 set @sqlstr2=''
20 WHILE EXISTS(SELECT code FROM @Cus)
21 BEGIN
22 -- 也可以使用top 1
23 SET ROWCOUNT 1
24 SELECT @tempcode= code, @tempname= name FROM @Cus;
25 set @sqlstr1= @sqlstr1 + 'sum(['+@tempname+']) as ['+@tempname+'],';
26 set @sqlstr2=@sqlstr2 + 'case CustomerCode when '''+@tempcode +''' then sum(qty) else 0 end as '''+@tempname+''',';
27 SET ROWCOUNT 0
28 DELETE FROM @Cus WHERE code=@tempcode;
29 END
30 --第一版数据
31 if(@month='1')
32 begin
33 set @sqlstrall='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
34 where ImportMonth='+CONVERT(VARCHAR(50),11)+' and PlanMonth='+@month+' and year='+Convert(varchar,Convert(int,@year)-1)+'
35 group by CustomerCode,ProductCode) s
36 group by ProductCode ';
37 end
38 else if(@month='2')
39 begin
40 set @sqlstrall='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
41 where ImportMonth='+CONVERT(VARCHAR(50),12)+' and PlanMonth='+@month+' and year='+Convert(varchar,Convert(int,@year)-1)+'
42 group by CustomerCode,ProductCode) s
43 group by ProductCode ';
44 end
45 else
46 begin
47 set @sqlstrall='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
48 where ImportMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,@month)-2))+' and PlanMonth='+@month+' and year='+@year+'
49 group by CustomerCode,ProductCode) s
50 group by ProductCode ';
51 end
52
53 --第二版数据
54 if(@month='2')
55 begin
56 set @sqlstrall2='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
57 where ImportMonth='+CONVERT(VARCHAR(50),11)+' and PlanMonth='+@month+' and year='+Convert(varchar,Convert(int,@year)-1)+'
58 group by CustomerCode,ProductCode) s
59 group by ProductCode ';
60 end
61 else
62 begin
63 set @sqlstrall2='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
64 where ImportMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,@month)-1))+' and PlanMonth='+@month+' and year='+@year+'
65 group by CustomerCode,ProductCode) s
66 group by ProductCode ';
67 end
68
69 --第三版数据
70
71 set @sqlstrall3='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
72 where ImportMonth='+@month+' and PlanMonth='+@month+' and year='+@year+'
73 group by CustomerCode,ProductCode) s
74 group by ProductCode ';
75 --第四版数据
76 if(@month='12')
77 begin
78 set @sqlstrall4='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
79 where ImportMonth='+@month+' and PlanMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,1)))+' and year='+Convert(varchar,Convert(int,@year)+1)+'
80 group by CustomerCode,ProductCode) s
81 group by ProductCode ';
82 end
83 else
84 begin
85 set @sqlstrall4='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
86 where ImportMonth='+@month+' and PlanMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,@month)+1))+' and year='+Convert(varchar,Convert(int,@year))+'
87 group by CustomerCode,ProductCode) s
88 group by ProductCode ';
89 end
90
91 --第五版数据
92 if(@month='12')
93 begin
94 set @sqlstrall5='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
95 where ImportMonth='+@month+' and PlanMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,2)))+' and year='+Convert(varchar,Convert(int,@year)+1)+'
96 group by CustomerCode,ProductCode) s
97 group by ProductCode ';
98 end
99 else if(@month='11')
100 begin
101 set @sqlstrall5='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
102 where ImportMonth='+@month+' and PlanMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,1)))+' and year='+Convert(varchar,Convert(int,@year)+1)+'
103 group by CustomerCode,ProductCode) s
104 group by ProductCode ';
105 end
106 else
107 begin
108 set @sqlstrall5='select ProductCode 产品代码, '+SUBSTRING(@sqlstr1,1,LEN(@sqlstr1)-1) +' from (select '+ @sqlstr2+'ProductCode from [Bus_Struct_RollingPlan]
109 where ImportMonth='+@month+' and PlanMonth='+CONVERT(VARCHAR(50),(CONVERT(INT,@month)+2))+' and year='+@year+'
110 group by CustomerCode,ProductCode) s
111 group by ProductCode ';
112 end
113
114 exec (@sqlstrall);
115 exec (@sqlstrall2);
116 exec (@sqlstrall3);
117 exec (@sqlstrall4);
118 exec (@sqlstrall5);
View Code
这里有一个小问题:where 后面的年份如果是int的会报错,所以要转换未varchar类型,因为表字段类型是varchar.记得所有我们是int类型的化,sql会默认转换的,不知道为什么会报错,最后还是统一转换类型好了。
最后的生成的某一条sql是这样子的
print @sqlstrall
print @sqlstrall2
print @sqlstrall3
print @sqlstrall4
select ProductCode 产品代码,
sum([太太乐郑州分公司]) as [太太乐郑州分公司],
sum([太太乐福州分公司]) as [太太乐福州分公司],
sum([太太乐武汉分公司]) as [太太乐武汉分公司],
sum([太太乐南昌分公司]) as [太太乐南昌分公司],
sum([太太乐青岛分公司]) as [太太乐青岛分公司],
sum([太太乐芜湖分公司]) as [太太乐芜湖分公司],
sum([太太乐烟台分公司]) as [太太乐烟台分公司],
sum([太太乐济南分公司]) as [太太乐济南分公司],
sum([上海东铪商贸有限公司(绍兴)]) as [上海东铪商贸有限公司(绍兴)],
sum([上海东铪商贸有限公司(无锡)]) as [上海东铪商贸有限公司(无锡)],
sum([上海东铪商贸有限公司(总部零售)]) as [上海东铪商贸有限公司(总部零售)],
sum([太太乐嘉兴分公司]) as [太太乐嘉兴分公司],
sum([太太乐长沙分公司]) as [太太乐长沙分公司],
sum([太太乐盐城分公司]) as [太太乐盐城分公司],
sum([太太乐南京分公司]) as [太太乐南京分公司],
sum([太太乐宁波分公司]) as [太太乐宁波分公司],
sum([太太乐南通分公司]) as [太太乐南通分公司],
sum([太太乐台州分公司]) as [太太乐台州分公司],
sum([太太乐徐州分公司]) as [太太乐徐州分公司],
sum([太太乐苏州分公司]) as [太太乐苏州分公司],
sum([太太乐厦门分公司]) as [太太乐厦门分公司],
sum([太太乐泉州分公司]) as [太太乐泉州分公司],
sum([太太乐温州分公司]) as [太太乐温州分公司],
sum([太太乐金华分公司]) as [太太乐金华分公司],
sum([太太乐杭州分公司]) as [太太乐杭州分公司],
sum([上海东铪商贸有限公司(苍南)]) as [上海东铪商贸有限公司(苍南)],
sum([太太乐扬州分公司]) as [太太乐扬州分公司],
sum([太太乐无锡(销售)分公司]) as [太太乐无锡(销售)分公司],
sum([上海东铪商贸有限公司]) as [上海东铪商贸有限公司],
sum([太太乐南宁分公司]) as [太太乐南宁分公司],
sum([太太乐广州分公司]) as [太太乐广州分公司],
sum([太太乐成都分公司]) as [太太乐成都分公司],
sum([太太乐兰州分公司]) as [太太乐兰州分公司],
sum([太太乐西安分公司]) as [太太乐西安分公司],
sum([太太乐贵阳分公司]) as [太太乐贵阳分公司],
sum([太太乐太原分公司]) as [太太乐太原分公司],
sum([太太乐北京分公司]) as [太太乐北京分公司],
sum([太太乐哈尔滨分公司]) as [太太乐哈尔滨分公司],
sum([太太乐石家庄分公司]) as [太太乐石家庄分公司],
sum([太太乐大连分公司]) as [太太乐大连分公司],
sum([太太乐长春分公司]) as [太太乐长春分公司],
sum([太太乐沈阳分公司]) as [太太乐沈阳分公司],
sum([太太乐天津分公司]) as [太太乐天津分公司],
sum([太太乐昆明分公司]) as [太太乐昆明分公司],
sum([太太乐合肥分公司]) as [太太乐合肥分公司],
sum([太太乐西安外埠分公司]) as [太太乐西安外埠分公司],
sum([太太乐洛阳分公司]) as [太太乐洛阳分公司],
sum([太太乐锦州分公司]) as [太太乐锦州分公司],
sum([太太乐齐齐哈尔分公司]) as [太太乐齐齐哈尔分公司],
sum([太太乐自贡分公司]) as [太太乐自贡分公司],
sum([太太乐阜阳分公司]) as [太太乐阜阳分公司],
sum([太太乐乌鲁木齐分公司]) as [太太乐乌鲁木齐分公司],
sum([太太乐呼和浩特分公司]) as [太太乐呼和浩特分公司],
sum([太太乐重庆分公司]) as [太太乐重庆分公司],
sum([太太乐深圳分公司]) as [太太乐深圳分公司],
sum([太太乐宜昌分公司]) as [太太乐宜昌分公司],
sum([太太乐银川分公司]) as [太太乐银川分公司] from (
select
case CustomerCode when '7G00313' then sum(qty) else 0 end as '太太乐郑州分公司',
case CustomerCode when '7G00325' then sum(qty) else 0 end as '太太乐福州分公司',
case CustomerCode when '7G00312' then sum(qty) else 0 end as '太太乐武汉分公司',
case CustomerCode when '7G00331' then sum(qty) else 0 end as '太太乐南昌分公司',
case CustomerCode when '7G00311' then sum(qty) else 0 end as '太太乐青岛分公司',
case CustomerCode when '7G00341' then sum(qty) else 0 end as '太太乐芜湖分公司',
case CustomerCode when '7G00342' then sum(qty) else 0 end as '太太乐烟台分公司',
case CustomerCode when '7G00310' then sum(qty) else 0 end as '太太乐济南分公司',
case CustomerCode when '7G00401' then sum(qty) else 0 end as '上海东铪商贸有限公司(绍兴)',
case CustomerCode when '7G00431' then sum(qty) else 0 end as '上海东铪商贸有限公司(无锡)',
case CustomerCode when '7G00416' then sum(qty) else 0 end as '上海东铪商贸有限公司(总部零售)',
case CustomerCode when '7G00332' then sum(qty) else 0 end as '太太乐嘉兴分公司',
case CustomerCode when '7G00330' then sum(qty) else 0 end as '太太乐长沙分公司',
case CustomerCode when '7G00307' then sum(qty) else 0 end as '太太乐盐城分公司',
case CustomerCode when '7G00306' then sum(qty) else 0 end as '太太乐南京分公司',
case CustomerCode when '7G00333' then sum(qty) else 0 end as '太太乐宁波分公司',
case CustomerCode when '7G00354' then sum(qty) else 0 end as '太太乐南通分公司',
case CustomerCode when '7G00340' then sum(qty) else 0 end as '太太乐台州分公司',
case CustomerCode when '7G00336' then sum(qty) else 0 end as '太太乐徐州分公司',
case CustomerCode when '7G00305' then sum(qty) else 0 end as '太太乐苏州分公司',
case CustomerCode when '7G00346' then sum(qty) else 0 end as '太太乐厦门分公司',
case CustomerCode when '7G00360' then sum(qty) else 0 end as '太太乐泉州分公司',
case CustomerCode when '7G00302' then sum(qty) else 0 end as '太太乐温州分公司',
case CustomerCode when '7G00301' then sum(qty) else 0 end as '太太乐金华分公司',
case CustomerCode when '7G00300' then sum(qty) else 0 end as '太太乐杭州分公司',
case CustomerCode when '7G00245' then sum(qty) else 0 end as '上海东铪商贸有限公司(苍南)',
case CustomerCode when '7G00304' then sum(qty) else 0 end as '太太乐扬州分公司',
case CustomerCode when '7G00303' then sum(qty) else 0 end as '太太乐无锡(销售)分公司',
case CustomerCode when '7G00241' then sum(qty) else 0 end as '上海东铪商贸有限公司',
case CustomerCode when '7G00326' then sum(qty) else 0 end as '太太乐南宁分公司',
case CustomerCode when '7G00324' then sum(qty) else 0 end as '太太乐广州分公司',
case CustomerCode when '7G00327' then sum(qty) else 0 end as '太太乐成都分公司',
case CustomerCode when '7G00323' then sum(qty) else 0 end as '太太乐兰州分公司',
case CustomerCode when '7G00322' then sum(qty) else 0 end as '太太乐西安分公司',
case CustomerCode when '7G00328' then sum(qty) else 0 end as '太太乐贵阳分公司',
case CustomerCode when '7G00314' then sum(qty) else 0 end as '太太乐太原分公司',
case CustomerCode when '7G00315' then sum(qty) else 0 end as '太太乐北京分公司',
case CustomerCode when '7G00318' then sum(qty) else 0 end as '太太乐哈尔滨分公司',
case CustomerCode when '7G00317' then sum(qty) else 0 end as '太太乐石家庄分公司',
case CustomerCode when '7G00321' then sum(qty) else 0 end as '太太乐大连分公司',
case CustomerCode when '7G00320' then sum(qty) else 0 end as '太太乐长春分公司',
case CustomerCode when '7G00319' then sum(qty) else 0 end as '太太乐沈阳分公司',
case CustomerCode when '7G00316' then sum(qty) else 0 end as '太太乐天津分公司',
case CustomerCode when '7G00329' then sum(qty) else 0 end as '太太乐昆明分公司',
case CustomerCode when '7G00308' then sum(qty) else 0 end as '太太乐合肥分公司',
case CustomerCode when '7G00361' then sum(qty) else 0 end as '太太乐西安外埠分公司',
case CustomerCode when '7G00358' then sum(qty) else 0 end as '太太乐洛阳分公司',
case CustomerCode when '7G00352' then sum(qty) else 0 end as '太太乐锦州分公司',
case CustomerCode when '7G00351' then sum(qty) else 0 end as '太太乐齐齐哈尔分公司',
case CustomerCode when '7G00348' then sum(qty) else 0 end as '太太乐自贡分公司',
case CustomerCode when '7G00309' then sum(qty) else 0 end as '太太乐阜阳分公司',
case CustomerCode when '7G00338' then sum(qty) else 0 end as '太太乐乌鲁木齐分公司',
case CustomerCode when '7G00337' then sum(qty) else 0 end as '太太乐呼和浩特分公司',
case CustomerCode when '7G00334' then sum(qty) else 0 end as '太太乐重庆分公司',
case CustomerCode when '7G00343' then sum(qty) else 0 end as '太太乐深圳分公司',
case CustomerCode when '7G00345' then sum(qty) else 0 end as '太太乐宜昌分公司',
case CustomerCode when '7G00344' then sum(qty) else 0 end as '太太乐银川分公司',
ProductCode
from [Bus_Struct_RollingPlan]
where ImportMonth=12 and PlanMonth=1 and year=2022
group by CustomerCode,ProductCode) s
group by ProductCode
需要注意的列名是不能有括号的,所以要全部加上中括号。有括号的原因就是,在维护基础数据的时候就有括号,这个无法避免。