行转列-高级

Test 数据库

declare @sql varchar(8000)
declare @sql2 varchar(8000)


set @sql=''  
set @sql2=''  

select @sql=@sql+','+code from ExtentData group by code

select @sql2=@sql2+' ,(select max('+a.Code+') from  data  where data.RequestID=Data2.RequestID) as '+b.Name  from ExtentData  as a
left join ExtentProperty as b on a.Code=b.Code
group by a.code,b.Name


set @sql=stuff(@sql,1,1,'')--去掉首个','


set @sql='   with data as ( select * from  [ExtentData]  pivot (max(Value) for code in ('+@sql+') ) a) ,data2 as (
 select RequestID '+@sql2+'
 from data as Data2  group by RequestID) select * from PatientSurgeryRequest as x left join data2 on x.ID=data2.RequestID '

print @sql
exec(@sql)