select custcode,summary,amount,amountin,amountout,balance
into #t1
from(
select '001' as custcode, '摘要01-1' as summary, 400 as amount, 20 as amountin, 30 as amountout, 390 as balance
union all
select '001' as custcode, '摘要02' as summary, 500 as amount, 10 as amountin, 50 as amountout, 460 as balance
union all
select '002' as custcode, '摘要01-1' as summary, 600 as amount, 40 as amountin, null as amountout, 640 as balance
union all
select '003' as custcode, '摘要02' as summary, 700 as amount, NULL as amountin, 40 as amountout, 660 as balance
union all
select '003' as custcode, '摘要01-1' as summary, 800 as amount, 50 as amountin, null as amountout, 850 as balance
union all
select '004' as custcode, '摘要03' as summary, 900 as amount, NULL as amountin, null as amountout, 900 as balance
) p
select * from #t1
declare @sql varchar(max),@decl varchar(200)
set @sql=null
select @sql=isnull(@sql+',','')+summary from #t1 group by summary
print(@sql)
set @decl='##t3'
set @sql='select *
into '+@decl+' from #t1 pivot (max(amount) for summary in ('+@sql+')) a'
print(@sql)
select rowid=identity(int,1,1),summary
into #t2
from #t1 group by summary
declare @summary varchar(2000),@rowid int,@sqsummary varchar(max)
set @sqsummary=''
while exists (select rowid from #t2)
begin
select @summary='', @rowid=null
select top 1 @rowid=rowid,@summary=summary from #t2 order by rowid asc
set @sqsummary=@sqsummary+',sum('+@summary+') as '+@summary
delete from #t2 where rowid=@rowid
end
print(@sqsummary)
--SELECT * FROM
--(SELECT * FROM #t1 ) AS b
--PIVOT ( min(amount) for summary in(??01,??02,??03)) AS pvt
exec(@sql)
declare @sql1 varchar(max)
set @sql1='select custcode,sum(amountin) as amountin,sum(amountout) as amountout,sum(balance) as balance '+@sqsummary+'
from ##t3 group by custcode'
print(@sql1)
exec(@sql1)
--select custcode,sum(amountin),sum(amoutout),sum(balance) from
drop table #t1
drop table #t2
drop table ##t3
运行出现错误：
消息 102，级别 15，状态 1，第 2 行
“-”附近有语法错误。
是由于表#t1.summary的数据是“摘要01-1”，数据有一个“-”这个问题，这个如何解决？
------解决思路----------------------
select custcode,summary,amount,amountin,amountout,balance你有些地方不规整，列名最好用[ ] 括起来
into #t1
from(
select '001' as custcode, '摘要01-1' as summary, 400 as amount, 20 as amountin, 30 as amountout, 390 as balance
union all
select '001' as custcode, '摘要02' as summary, 500 as amount, 10 as amountin, 50 as amountout, 460 as balance
union all
select '002' as custcode, '摘要01-1' as summary, 600 as amount, 40 as amountin, null as amountout, 640 as balance
union all
select '003' as custcode, '摘要02' as summary, 700 as amount, NULL as amountin, 40 as amountout, 660 as balance
union all
select '003' as custcode, '摘要01-1' as summary, 800 as amount, 50 as amountin, null as amountout, 850 as balance
union all
select '004' as custcode, '摘要03' as summary, 900 as amount, NULL as amountin, null as amountout, 900 as balance
) p
select * from #t1
declare @sql varchar(max),@decl varchar(200)
set @sql=null
select @sql=isnull(@sql+',','')+'['+summary+']' from #t1 group by summary
print(@sql)
set @decl='##t3'
set @sql='select *
into '+@decl+' from #t1 pivot (max(amount) for summary in ('+@sql+')) a'
print(@sql)
select rowid=identity(int,1,1),summary
into #t2
from #t1 group by summary
declare @summary varchar(2000),@rowid int,@sqsummary varchar(max)
set @sqsummary=''
while exists (select rowid from #t2)
begin
select @summary='', @rowid=null
select top 1 @rowid=rowid,@summary=summary from #t2 order by rowid asc
set @sqsummary=@sqsummary+',sum(['+@summary+']) as ['+@summary+']'
delete from #t2 where rowid=@rowid
end
print(@sqsummary)
--SELECT * FROM
--(SELECT * FROM #t1 ) AS b
--PIVOT ( min(amount) for summary in(??01,??02,??03)) AS pvt
exec(@sql)
declare @sql1 varchar(max)
set @sql1='select custcode,sum(amountin) as amountin,sum(amountout) as amountout,sum(balance) as balance '+@sqsummary+'
from ##t3 group by custcode'
print(@sql1)
exec(@sql1)
--select custcode,sum(amountin),sum(amoutout),sum(balance) from
drop table #t1
drop table #t2
drop table ##t3