Sql Server行转列 动态拼接sql
要求导出的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);
这里有一个小问题：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
需要注意的列名是不能有括号的，所以要全部加上中括号。有括号的原因就是，在维护基础数据的时候就有括号，这个无法避免。
