多行合并成一行笔记
with #twmpTable as(
select
a.F_ID id,
a.F_Code code,
b.F_Name name,
b.F_SQZSL num,
0 price,
F_SQBMCode department
from t_wms_out a
left join t_wms_out_detail b on a.F_ID = b.F_OutId
where a.F_SQLX = '100'--100 = 出库申请单
and a.F_ZZId = '3001'
)
select * from #twmpTable
-------------------------初级版本----------------
with #twmpTable as(
select
a.F_ID id,
a.F_Code code,
b.F_Name name,
b.F_SQZSL num,
0 price,
F_SQBMCode department
from t_wms_out a
left join t_wms_out_detail b on a.F_ID = b.F_OutId
where a.F_SQLX = '100'
and a.F_ZZId = '3001'
)
Select
id,
stuff((select ','+name from #twmpTable where id=t.id for xml path('')),1,1,'') name
From #twmpTable t
Group by id
-------------------------最终版本----------------
with #twmpTable as(
select
a.F_ID id,
a.F_Code code,
b.F_Name name,
b.F_SQZSL num,
0 price,
F_SQBMCode department
from t_wms_out a
left join t_wms_out_detail b on a.F_ID = b.F_OutId
where a.F_SQLX = '100'--100 = 出库申请单
and a.F_ZZId = '3001'
)
Select
id,
code,
stuff((select ','+name from #twmpTable where id=t.id for xml path('')),1,1,'') name,
sum(num) num,
sum(price) price,
department
From #twmpTable t
Group by
id,
code,
department
版权声明：本文为kimizhou_blog原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。