热心网友
回答时间:2023-09-21 16:38
一般采用
select a.cname , b.dvalue from 表a a inner join 表b b on a.cid=b.cid
然后根据 cname 的值,来在外面程序中 判断。
给你个动态列的示例,你可以参考下,符合你的要求!
declare @name varchar(100), @sql_str varchar(8000),@crt_sql varchar(8000),@drp_sql varchar(8000),@join varchar(8000)
select @name = '', @sql_str = 'select a.cid ', @join = ' from 表a a ', @crt_sql = '',@drp_sql = ''
declare usercursor cursor
for select distinct cname from 表a
open usercursor
FETCH NEXT FROM usercursor into @name
while @@FETCH_STATUS = 0
begin
--读取下一行数据把读取的数据放在变量中
declare @tb varchar(50)
set @tb = 'tmptmp_'+@name
set @crt_sql = ' select cid, dvalue as '+@name+' into '+ @tb + ' from 表b where cid in ( select cid from 表a where cname = '''+@name+''') '
exec(@crt_sql)
set @sql_str = @sql_str + ' , '+@tb+'.'+@name
set @join = @join + ' left join '+@tb+' on a.cid = '+@tb+'.cid '
set @drp_sql = @drp_sql +' DROP TABLE '+ @tb + ' '
FETCH NEXT FROM usercursor into @name
end
close usercursor
deallocate usercursor
exec(@sql_str+@join)
exec(@drp_sql)
-- print @sql_str+@join
收起