create table tbluser(usercode varchar(5),userName varchar(10))
insert into tbluser
select 'A01','张三'
union all select 'A02','李四'
union all select 'A03','王五'
union all select 'A04','朱六'create table tblsalary(usercode varchar(5),other varchar(10),money int)
insert into tblsalary
select 'A01','书1',10
union all select 'A02','书2',12
union all select 'A04','书1',10
union all select 'A01','书1',15
union all select 'A04','书4',20
godeclare @s varchar(8000)
set @s=''
select @s=@s+',['+username+']=isnull(cast(sum(case usercode when '''+usercode
+''' then 1 end) as varchar),'''')'
from(select distinct a.username,usercode=cast(a.usercode as varchar)
from tbluser a join tblsalary b on a.usercode=b.usercode) a order by usercode
--exec('select other'+@s+',money=sum(money) from tblsalary group by other')
select 'select other'+@s+',money=sum(money) from tblsalary group by other'
go--删除测试的表
drop table tblsalary,tbluser
select other,[张三]=isnull(cast(sum(case usercode when 'A01' then 1 end) as varchar),''),[李四]=isnull(cast(sum(case usercode when 'A02' then 1 end) as varchar),''),[朱六]=isnull(cast(sum(case usercode when 'A04' then 1 end) as varchar),''),money=sum(money) from
insert into tbluser
select 'A01','张三'
union all select 'A02','李四'
union all select 'A03','王五'
union all select 'A04','朱六'create table tblsalary(usercode varchar(5),other varchar(10),money int)
insert into tblsalary
select 'A01','书1',10
union all select 'A02','书2',12
union all select 'A04','书1',10
union all select 'A01','书1',15
union all select 'A04','书4',20
godeclare @s varchar(8000)
set @s=''
select @s=@s+',['+username+']=isnull(cast(sum(case usercode when '''+usercode
+''' then 1 end) as varchar),'''')'
from(select distinct a.username,usercode=cast(a.usercode as varchar)
from tbluser a join tblsalary b on a.usercode=b.usercode) a order by usercode
--exec('select other'+@s+',money=sum(money) from tblsalary group by other')
select 'select other'+@s+',money=sum(money) from tblsalary group by other'
go--删除测试的表
drop table tblsalary,tbluser
select other,[张三]=isnull(cast(sum(case usercode when 'A01' then 1 end) as varchar),''),[李四]=isnull(cast(sum(case usercode when 'A02' then 1 end) as varchar),''),[朱六]=isnull(cast(sum(case usercode when 'A04' then 1 end) as varchar),''),money=sum(money) from
insert into tbluser
select 'A01','张三'
union all select 'A02','李四'
union all select 'A03','王五'
union all select 'A04','朱六'create table tblsalary(usercode varchar(5),other varchar(10),money int)
insert into tblsalary
select 'A01','书1',10
union all select 'A02','书2',12
union all select 'A04','书1',10
union all select 'A01','书1',15
union all select 'A04','书4',20
godeclare @s varchar(8000)
set @s=''
select @s=@s+',['+username+']=isnull(cast(sum(case usercode when '''+usercode
+''' then 1 end) as varchar),'''')'
from(select distinct a.username,usercode=cast(a.usercode as varchar)
from tbluser a join tblsalary b on a.usercode=b.usercode) a order by usercode
exec('select other'+@s+',money=sum(money) from tblsalary group by other')go--删除测试的表
drop table tblsalary,tbluser
declare @s varchar(8000)
set @s='select other'
select @s=@s+',['+username+']=isnull(cast(sum(case usercode when '''+usercode
+''' then 1 end) as varchar),'''')'
from(select distinct a.username,usercode=cast(a.usercode as varchar)
from tbluser a join tblsalary b on a.usercode=b.usercode) a order by usercode
set @s=@s + ',money=sum(money) from tblsalary group by other'
-- print @s -- 说明:当发现错误的时候,你把拼出来的@s打印出来,然后贴到查询分析器中去执行,然后根据语句的结构多回几次车,这样就很容易看出错误是在哪里了。
exec(@s)
go
如 @a varchar(10),那么构造语句的时候需要... ''' + @a + ''' ... -- 靠近+号两边的'号码是用来连接字符串的。