动态Sql获取动态表数据

转自 http://www.cnblogs.com/zhuisuo/archive/2010/11/23/1885623.html

 

if object_id('country') is not null
 drop table country
Go
Create Table country( id int null, name varchar(50) null)

if object_id('Product') is not null
drop table Product
go
create table Product( id int null, countryid int null, name varchar(50) null)

insert Into country values(1,'中国');
insert Into country values(2,'美国');
insert Into country values(3,'英国');
insert Into country values(4,'法国');
insert Into country values(5,'日本');
Insert into Product values(1,1,'产品1');
Insert into Product values(2,2,'产品2');
Insert into Product values(3,3,'产品1');
Insert into Product values(4,7,'产品3');
go

declare @id  varchar(100),@name varchar(100)
declare @sql varchar(max)
set @sql='select name'
declare name_zuisuo cursor for
select id,name from country
open name_zuisuo
fetch name_zuisuo into @id,@name
while @@fetch_status=0
begin
 set @sql=@sql+',max(case when countryid='+char(39)+@id+char(39)
 +' then '+char(39)+'yes'+char(39)+'else '+char(39)+'no'+char(39)+' end) as '
 +char(39)+@name+char(39)+''
 fetch name_zuisuo into @id,@name
end
close name_zuisuo
deallocate name_zuisuo
set @sql=@sql+' from product group by name'
print @sql
exec (@sql)

--
--select * from product
--select name,
--max(case when countryid='1' then 'yes'else 'no' end) as '中国'
--,max(case when countryid='2' then 'yes'else 'no' end) as '美国'
--,max(case when countryid='3' then 'yes'else 'no' end) as '英国'
--,max(case when countryid='4' then 'yes'else 'no' end) as '法国'
--,max(case when countryid='5' then 'yes'else 'no' end) as '日本'
--from product group by name
--------------------------------------------------

上面的可能不是最好的，但是相信在自己的ERP工作会用到。、

感谢作者