Table1
字段 objid column value
1 列1 12
2 列2 123
3 列3 1234
Table2
字段 objid name
1 Me
2 You
3 He结果要求
objid name 列1 列2 列3
1 Me 12 null null
2 You null 123 null
3 He null null 1234
字段 objid column value
1 列1 12
2 列2 123
3 列3 1234
Table2
字段 objid name
1 Me
2 You
3 He结果要求
objid name 列1 列2 列3
1 Me 12 null null
2 You null 123 null
3 He null null 1234
insert into tb select 1,'列1',12
union all select 2,'列2',123
union all select 3,'列3',1234create table tt(objid int,name varchar(10))
insert into tt select 1,'Me'
union all select 2,'You'
union all select 3,'He'
go--静态
select a.objid,
a.name,
[列1]=sum(case [column] when '列1' then value else null end),
[列2]=sum(case [column] when '列2' then value else null end),
[列3]=sum(case [column] when '列3' then value else null end)
from tt a,tb b
where a.objid=b.objid
group by a.objid,a.name
order by a.objid--动态
declare @sql varchar(8000)
set @sql='select a.objid,a.name'
select @sql=@sql+',['+[column]+']=sum(case [column] when '''+[column]+''' then value else null end)' from tb group by [column]
exec(@sql+' from tt a,tb b where a.objid=b.objid group by a.objid,a.name order by a.objid') drop table tt,tb