create table 表a(name varchar(10),number int)--如果对于每个name,number可能重复,用临时表:
select id=identity(int,1,1),* into #t from 表adeclare @s varchar(8000),@i int
select @s='',@i=max(aa) from(
select aa=count(*) from #t group by name
)awhile @i>0
select @s=',number'+cast(@i as varchar)
+'=max(case n when '+cast(@i as varchar) --改为max
+' then number else '''' end)'+@s
,@i=@i-1
exec('select name'+@s+' from(
select name,number,n=(
select sum(1) from #t
where name=a.name and id<=a.id)
from #t a
)a group by name')drop table #t
select id=identity(int,1,1),* into #t from 表adeclare @s varchar(8000),@i int
select @s='',@i=max(aa) from(
select aa=count(*) from #t group by name
)awhile @i>0
select @s=',number'+cast(@i as varchar)
+'=max(case n when '+cast(@i as varchar) --改为max
+' then number else '''' end)'+@s
,@i=@i-1
exec('select name'+@s+' from(
select name,number,n=(
select sum(1) from #t
where name=a.name and id<=a.id)
from #t a
)a group by name')drop table #t
+'=sum(case n when '+cast(@i as varchar)
+' then number else 0 end)'+@s
,@i=@i-1
我早就试过了不行
set @a='20.00'
select @a
select cast(cast(@a as decimal(38,0)) as int)
--------测试-----------
20
create table 表a(name varchar(10),number varchar(10)时,就会提示上面的错误!
因为我希望零要显示出来!
select @s='',@i=max(aa) from(
select aa=count(*) from #t group by name
)awhile @i>0
select @s=',number'+cast(@i as varchar)
+'=cast(sum(case n when '+cast(@i as varchar)
+' then number else 0 end) as varchar)'+@s
,@i=@i-1
exec('select name'+@s+' from(
select name,number,n=(
select sum(1) from #t
where name=a.name and id<=a.id)
from #t a
)a group by name')drop table #t
我还用了下面的语句,
'=max(cast((case n when '+cast(@i as varchar)+' then number else 0 end) as varchar)) '
反正总是提示上面说的错误, 难道真的没有办法改吗?
将下面中的
exec('select name'+@s+' from(
select name,number,n=(
select sum(1) from #t
where name=a.name and id<=a.id)
from #t a
)a group by name')改为
cast(number as real) as number1 才不会提示错误!
但是零就去掉了,不知道还有没有别的方法啊??
真的很着急