select CodeName FROM CodeDictionary where CodeCategory_ID=138 结果:
开始转换: DECLARE@sqlVARCHAR(8000)
SELECT@sql=isnull(@sql+',','')+CodeName FROM CodeDictionary where CodeCategory_ID=138GROUPBY CodeName
SET@sql='select * from (select ch.HousebillID,ch.Chargeamount,cd.CodeCategory_ID,cd.CodeName
from Housebill hb left join Chargebill ch on hb.ID=ch.HousebillID left join CodeDictionary cd
on ch.Chargeitem=cd.ID where cd.CodeCategory_ID=138) tb pivot (max(tb.Chargeamount) for tb.CodeName in ('+@sql+'))a'exec(@sql)
结果就类似这样的语句: select*from (
select ch.HousebillID,ch.Chargeamount,cd.CodeCategory_ID,cd.CodeName
from Housebill hb leftjoin Chargebill ch on hb.ID=ch.HousebillID leftjoin CodeDictionary cd on ch.Chargeitem=cd.ID where cd.CodeCategory_ID=138
)
tb pivot (sum(tb.Chargeamount)
for tb.CodeName in (保洁费,材料费,电费,服务费,宽带,其他,燃气费,水费,维修费,卫生,物业费,押金,优惠,有线电视,租金)
)a
where CodeCategory_ID=138