SQL批量行转列已经多个字段拼接

第二步：运行一下SQL,就可以得出以下拼接(合并)效果。关键点是where jobId=V_WIP_Balance.jobId   。你想要第一个显示什么，这个ID就要在视图中查出来，不然会数据有误，而且查询速度非常慢。

select 
S_Customer.nickName 客户,
S_Job.partNum 生产型号,
isnull(SO_Num.订单数 - CPCK.出库数量,0) 欠数,
BalanceNum=stuff((select ' '+BalanceNum FROM V_WIP_Balance t 
where jobId=V_WIP_Balance.jobId  FOR xml path('')), 1, 1, '')  


FROM V_WIP_Balance  
LEFT JOIN S_Job ON S_Job.recId=V_WIP_Balance.jobId
LEFT JOIN S_Customer ON S_Customer.recId = S_Job.customerId
---订单总数
left JOIN (
SELECT 
S_ContractItem.jobId,
SUM(isnull(S_ContractItem.qty_Order,0)) 订单数


 FROM S_ContractItem
GROUP BY S_ContractItem.jobId


) SO_Num ON SO_Num.jobId = S_Job.recId
--出库数
LEFT JOIN (
SELECT
FPSitem.jobId,
SUM(ISNULL(FGI_InventoryOut.qtyofPCS,0)) 出库数量
FROM
FGI_PackingSlip FPS
LEFT JOIN FGI_PackingSlipItem FPSitem ON FPSitem.packingSlipId = FPS.recId   --出货计划明细单
LEFT JOIN FGI_InventoryOut ON FGI_InventoryOut.packingSlipItemId = FPSitem.recId
where FPS.ifassign=1
GROUP BY FPSitem.jobId
) CPCK ON CPCK.jobId = S_Job.recId


--WHERE S_Job.partNum='1F10904C1'
group by V_WIP_Balance.jobId,S_Customer.nickName,S_Job.partNum,SO_Num.订单数,CPCK.出库数量


ORDER BY S_Job.partNum