改一下,注('''+ box_id + ' ''then) 跟原业的区别:
create table #test (contract_number varchar(20), box_id varchar(20), box_num int)
go
insert into #test select '00电0634', '01', 2 union all
select '00电0634', '02-1', 1 union all
select '00电0634', '02-2', 1 union all
select '00电0634', '03' , 3 union all
select '00电0988', '01' , 1 union all
select '00电0988', '02' , 1 union all select '00日0566', '01', 2 union all
select '00日0566', '02', 2 union all
select '00日0566', '03' , 2 union all
select '00日0567', '01-1' , 1 union all
select '00日0567', '01-2' , 1 union all
select '00日0567', '02' , 2 go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+box_id+']=sum(case box_id when '''+ box_id + ' ''then box_num else 0 end)'
from (select distinct box_id=cast(box_id as varchar) from #test) aprint @s
exec('select contract_number'+@s+' from #test group by contract_number')
godrop table #test
create table #test (contract_number varchar(20), box_id varchar(20), box_num int)
go
insert into #test select '00电0634', '01', 2 union all
select '00电0634', '02-1', 1 union all
select '00电0634', '02-2', 1 union all
select '00电0634', '03' , 3 union all
select '00电0988', '01' , 1 union all
select '00电0988', '02' , 1 union all select '00日0566', '01', 2 union all
select '00日0566', '02', 2 union all
select '00日0566', '03' , 2 union all
select '00日0567', '01-1' , 1 union all
select '00日0567', '01-2' , 1 union all
select '00日0567', '02' , 2 go
declare @s varchar(8000)
set @s=''
select @s=@s+',['+box_id+']=sum(case box_id when '''+ box_id + ' ''then box_num else 0 end)'
from (select distinct box_id=cast(box_id as varchar) from #test) aprint @s
exec('select contract_number'+@s+' from #test group by contract_number')
godrop table #test
解决方案 »
- SQL Server 安装程序无法运行。另一个 SQL Server 安装程序实例已在运行。
- 求一插入触发器写法
- SQL2000如何备份数据库
- 那位能给我讲讲bcp的详细用法!最好能举个实例!谢谢!
- varchar(5)能提取超过5个字符的数据吗?
- 请教:在程序中使用到一个查询的时候突然出现“SQL 605 致命错误”是怎么回事?
- WIN2000不能安装SQL SERVER????
- 在线等待,搞定立即结贴,50!!
- SQL怎样完成这样的查询?
- 求救,在分析服务器(Analysis Manager)中按照【Analysis Manager 概念与教程】指导建立了一个【教程】的新数据库,出了问题
- 【问高手】多线程能不能提高SQL server查询效率?
- 在delphi中如何使用sql-dmo实现数据库的备份与还原
set @s=''
select @s=@s+',['+box_id+']=sum(case box_id when '''+box_id+''' then box_num else 0 end)'
from Table1 group by box_id
exec('select contract_number'+@s+' from Table1 group by contract_number')