declare @i int,@s varchar(8000)
select @i=0,@s=''
select @i=@i+1
,@s=@s+'类型'+cast(@i as varchar)+'='''+类型+''',单价'
+cast(@i as varchar)+'=sum(case 类型 when '''+类型
+''' then 单价 else 0 end)'
from 表
group by 类型
exec('select 编号'+@s+' from 表 group by 编号')
select @i=0,@s=''
select @i=@i+1
,@s=@s+'类型'+cast(@i as varchar)+'='''+类型+''',单价'
+cast(@i as varchar)+'=sum(case 类型 when '''+类型
+''' then 单价 else 0 end)'
from 表
group by 类型
exec('select 编号'+@s+' from 表 group by 编号')
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: '类型2' 附近有语法错误。麻烦楼上的能不能帮忙给点解释,在下比较菜,特别是这里:
select @i=@i+1
,@s=@s+'类型'+cast(@i as varchar)+'='''+类型+''',单价'
+cast(@i as varchar)+'=sum(case 类型 when '''+类型
+''' then 单价 else 0 end)'再次谢谢!!!
insert #aa select '001','AAB',1.01
union all select '001','AAC',2.02
union all select '001','AAD',3.03
union all select '002','AAB',1.01
union all select '002','AAC',2.02
union all select '002','AAD',3.03
declare @i int,@s varchar(8000)
select @i=0,@s=''
select @i=@i+1
,@s=@s+'类型'+cast(@i as varchar)+'='''+类型+''',单价'
+cast(@i as varchar)+'=sum(case 类型 when '''+类型
+''' then 单价 else 0 end),'
from #aa
group by 类型
select @s=left(@s,len(@s)-1)
select @s
exec('select 编号,'+@s+' from #aa group by 编号')
--结果:
编号 类型1 单价1 类型2 单价2 类型3 单价3
-------- ---- -------------------- --------------------
001 AAB 1.01 AAC 2.02 AAD 3.03
002 AAB 1.01 AAC 2.02 AAD 3.03
create table 表(编号 char(3),类型 varchar(10),单价 decimal(10,1))
insert 表 select '001','AAB',1.01
union all select '001','AAC',2.02
union all select '001','AAD',3.03
union all select '002','AAB',1.01
union all select '002','AAC',2.02
union all select '002','AAD',3.03
go--查询
declare @i int,@s varchar(8000)
select @i=0,@s=''
select @i=@i+1
,@s=@s+',类型'+cast(@i as varchar)+'='''+类型+''',单价'
+cast(@i as varchar)+'=sum(case 类型 when '''+类型
+''' then 单价 else 0 end)'
from 表
group by 类型
exec('select 编号'+@s+' from 表 group by 编号')
go--删除测试
drop table 表/*--测试结果
编号 类型1 单价1 类型2 单价2 类型3 单价3
---- ------ --------- ------ --------- ------ ---------
001 AAB 1.0 AAC 2.0 AAD 3.0
002 AAB 1.0 AAC 2.0 AAD 3.0--*/