SQL中列轉行的問題
測試環境:
create table tb(單位名稱 varchar(10),日期 datetime,銷售額 int)
insert into tb
select 'A單位','2001-01-01',100
union all select 'B單位','2001-01-02',101
union all select 'C單位','2001-01-03',102
union all select 'D單位','2001-01-04',103
union all select 'E單位','2001-01-05',104
union all select 'F單位','2001-01-06',105
union all select 'G單位','2001-01-07',106
union all select 'H單位','2001-01-08',107
union all select 'I單位','2001-01-09',108
union all select 'J單位','2001-01-11',109
insert into tb
select 'A單位','2001-01-01',100
union all select 'B單位','2001-01-02',101
union all select 'C單位','2001-01-03',102
union all select 'D單位','2001-01-04',103
union all select 'E單位','2001-01-05',104
union all select 'F單位','2001-01-06',105
union all select 'G單位','2001-01-07',106
union all select 'H單位','2001-01-08',107
union all select 'I單位','2001-01-09',108
union all select 'J單位','2001-01-11',109
以上建立表並插件測試資料.
下面要將列資料轉換成行資料,如下所示:
/*-- 要求結果
日期 A單位 B單位 C單位 D單位 E單位 F單位 G單位 H單位 I單位 J單位
---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*/
日期 A單位 B單位 C單位 D單位 E單位 F單位 G單位 H單位 I單位 J單位
---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*/
處理方法:
declare @sqlhead varchar(8000)
declare @sqlend varchar(8000)
declare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
declare @i int,@ic varchar(20)
--生成數據處理臨時表
select id=identity(int,0,1),gid=0
,a=',['+單位名稱 +']=sum(case 單位名稱 when '''
+單位名稱+''' then 銷售額 else 0 end)'
into # from(select distinct 單位名稱 from tb) a
--判斷需要多少個變量來處理
select @i=max(len(a)) from #
print @i
set @i=7800/@i
--分組臨時表
update # set gid=id/@i
select @i=max(gid) from #
--生成數據處理語句
select @sqlhead='''select 日期=convert(varchar(10),日期,120)'''
,@sqlend=''' from tb group by convert(varchar(10),日期,120)'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,8000)
--執行
exec(@sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')')
--刪除臨時表
drop table #
--*/
declare @sqlend varchar(8000)
declare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
declare @i int,@ic varchar(20)
--生成數據處理臨時表
select id=identity(int,0,1),gid=0
,a=',['+單位名稱 +']=sum(case 單位名稱 when '''
+單位名稱+''' then 銷售額 else 0 end)'
into # from(select distinct 單位名稱 from tb) a
--判斷需要多少個變量來處理
select @i=max(len(a)) from #
print @i
set @i=7800/@i
--分組臨時表
update # set gid=id/@i
select @i=max(gid) from #
--生成數據處理語句
select @sqlhead='''select 日期=convert(varchar(10),日期,120)'''
,@sqlend=''' from tb group by convert(varchar(10),日期,120)'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@ic
select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
,@sql2=left(@sql2,len(@sql2)-1)+char(13)
,@sql3=left(@sql3,len(@sql3)-1)
,@sql4=substring(@sql4,2,8000)
--執行
exec(@sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')')
--刪除臨時表
drop table #
--*/