for xml path与pivot 实现列转行的使用

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

原表：
实现效果：
方法：pivot
select * from (select allls.ihc ,sick_cir.sick_name,ksmc,name ,SUM(allls.mon) je from allls ,sick_cir,szks,szltmwhere szltm.code=allls.itc and itc in (‘06’,‘13’,‘16’,‘38’) and szks.ksbh=allls.ksbh and sick_cir.ihc =allls.ihc and allls.ihc in (select distinct ihc from allls where jsrq >‘2016-03-21’ and jsrq <‘2016-06-20’ and itc =‘06’ )group by sick_name,ksmc,name,allls.ihc --order by allls.ihc,ksmc,sick_name,name)t1 pivot (max(je)for name in (一次性材料,手术,术料,卫生材料)) t2 where ihc ='173511’order by ihc

还有其他更好的实现方法欢迎大佬留言。