for xml path与pivot 实现列转行的实例运用
原表:
实现效果:
方法:for xml path
select t3.ivc,t3.payee,LEFT(ld,LEN(ld)-1) from (
select t2.ivc,t2.payee,
(select t1.ld+’,’ from (
select cbrecipe.ivc,cbrecipe.payee,szldm.ld
from cbrecipe,cbrecipe_list ,szldm
where szldm.ldc =cbrecipe_list.ldc
and cbrecipe.ivc =cbrecipe_list.ivc )t1
where t2.ivc=t1.ivc and t2.payee =t1.payee for xml path(’’)) as ld from (select cbrecipe.ivc,cbrecipe.payee,szldm.ld
from cbrecipe,cbrecipe_list ,szldm where szldm.ldc =cbrecipe_list.ldc and cbrecipe.ivc =cbrecipe_list.ivc)t2 group by ivc,payee ) t3
原表: