sqlserver 字符串多行合并为一行

SELECT

S_OS_PO.recId,S_OS_PO.os_PO_Number as 外协单号,S_JOB.partnum as 生产编号,        P_MO.moNumber as 制造单号,

CASE WHEN T_SubcontractType.unitId=3 then S_OS_POItem.qty_PNL else S_OS_POItem.qty end as 外协数量

 

INTO #TB01

from S_OS_PO with (nolock)

left join S_OS_POItem with (nolock) on S_OS_POItem.os_PO_Id=S_OS_PO.recId

left join T_Plants WITH (nolock) on T_Plants.recId=S_OS_PO.plantsId

left join T_Company WITH (nolock) on T_Company.recId=T_Plants.companyId

left join M_Suppliers with (nolock) on S_OS_PO.suppliersId=M_Suppliers.recId

left join S_SalesParts with (nolock) on S_OS_POItem.salesPartId=S_SalesParts.recId

left join T_User with (nolock) on T_User.recId=S_OS_PO.creatorId

left join S_JOB with (nolock) on S_JOB.recId=S_OS_POItem.jobId

left join T_SubcontractType with (nolock) on  T_SubcontractType.recId=S_OS_POItem.subcontractTypeId

left join T_Unit  T_Unit with (nolock) on T_Unit.recId=T_SubcontractType.unitId

left join S_OS_POHistory with (nolock) on S_OS_POHistory.os_PO_Id=S_OS_PO.recId and taskName='部门负责人审核'

inner join S_OSWO with (nolock) on S_OSWO.poItemId=S_OS_POItem.recId

inner join P_wo pw with (nolock) on pw.recid=S_OSWO.woid

left join P_MO with (nolock) on P_MO.recId=pw.moid

 

--WHERE S_OS_PO.type='WO' and S_OS_PO.recID=?

 

WHERE S_OS_PO.type='WO' and S_OS_PO.os_PO_Number='OPO19041300409'

 

 

SELECT a.recId,a.外协单号,a.生产编号,a.外协数量,

制造单号=(

STUFF(

(SELECT ' # '+b.制造单号 FROM #TB01 b WHERE a.生产编号=b.生产编号

FOR XML PATH('')),1,1,''

)

)

FROM #TB01 a

GROUP BY a.recId,a.外协单号,a.生产编号,a.外协数量

DROP TABLE #TB01