create table t ([month] int,[date] int)insert t select 1,10 union all select 2,10 union all select 3,29 union all select 4,30 union all select 5,40declare @sql varchar(8000) set @sql='' select @sql=@sql+',['+cast([month] as varchar)+']=isnull(sum(case when [month]='+cast([month] as varchar)+' then [date] end),0)' from t group by [month] select @sql='select '+stuff(@sql,1,1,'')+' from t'exec(@sql)drop table t1 2 3 4 5 ----------- ----------- ----------- ----------- ----------- 10 10 29 30 40警告: 聚合或其它 SET 操作消除了空值。
结果是两个行排,重新调整就行了。 create table t ([month] varchar(4),[date] varchar(4))insert t select 1,10 union all select 2,10 union all select 3,29 union all select 4,30 union all select 5,40 select * from t declare toms cursor for select [month],[date] from t order by [month] open toms declare @i int declare @C_1 varchar(100) declare @C_2 varchar(100) declare @C_11 varchar(100) declare @C_22 varchar(100) set @i=5 --5为表行数 set @C_11='' set @C_22='' fetch next from toms into @C_1,@C_2 while @i>0 begin set @C_11=@C_11+convert(char(4),@C_1) set @C_22=@C_22+convert(char(4),@C_2) set @i=@i-1 fetch next from toms into @C_1,@C_2 end select @C_11,@C_22 select @C_1,@C_2 close toms go deallocate toms go drop table t go
([month] int,[date] int)insert t
select 1,10 union all
select 2,10 union all
select 3,29 union all
select 4,30 union all
select 5,40declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',['+cast([month] as varchar)+']=isnull(sum(case when [month]='+cast([month] as varchar)+'
then [date] end),0)'
from t group by [month]
select @sql='select '+stuff(@sql,1,1,'')+' from t'exec(@sql)drop table t1 2 3 4 5
----------- ----------- ----------- ----------- -----------
10 10 29 30 40警告: 聚合或其它 SET 操作消除了空值。
create table t
([month] varchar(4),[date] varchar(4))insert t
select 1,10 union all
select 2,10 union all
select 3,29 union all
select 4,30 union all
select 5,40
select * from t
declare toms cursor
for
select [month],[date] from t order by [month]
open toms
declare @i int
declare @C_1 varchar(100)
declare @C_2 varchar(100)
declare @C_11 varchar(100)
declare @C_22 varchar(100)
set @i=5 --5为表行数
set @C_11=''
set @C_22=''
fetch next from toms into @C_1,@C_2
while @i>0
begin
set @C_11=@C_11+convert(char(4),@C_1)
set @C_22=@C_22+convert(char(4),@C_2)
set @i=@i-1
fetch next from toms into @C_1,@C_2
end
select @C_11,@C_22
select @C_1,@C_2
close toms
go
deallocate toms
go
drop table t
go