stuff 字符串拼接

分组后拼接字符串

SELECT pgs.PtypeId,pgs.KtypeId,pgs.pgDetail,SUM(pgs.pgDetail)

,stuff((select 'ǒǜ'+convert(varchar(25), g.pgIDCode) FROM (SELECT pggoodsidcodes.* FROM dbo.pggoodsidcodes
LEFT JOIN tmp_PgChecked
ON pggoodsidcodes.pgBarCode=tmp_PgChecked.PgBarCode
WHERE pggoodsidcodes.PtypeId='00001' AND pggoodsidcodes.KtypeId='00001'
AND pggoodsidcodes.goodsorderid=1 AND pggoodsidcodes.pgDetail<>0
AND IsUsed=0 AND tmp_PgChecked.ptypeid IS NULL
) g
WHERE g.PtypeId = pgs.PtypeId AND g.KtypeId=pgs.KtypeId AND g.pgDetail=pgs.pgDetail AND g.goodsorderid=pgs.goodsorderid AND g.IsCut=pgs.IsCut
FOR XML PATH('')), 1,2, '') as CustomerName

FROM dbo.pggoodsidcodes pgs
LEFT JOIN tmp_PgChecked
ON pgs.pgBarCode=tmp_PgChecked.PgBarCode
where pgs.PtypeId='00001' AND pgs.KtypeId='00001'
AND pgs.goodsorderid=1 AND pgs.pgDetail<>0
AND IsUsed=0 AND tmp_PgChecked.ptypeid IS NULL
GROUP BY pgs.PtypeId,pgs.KtypeId,pgs.pgDetail,pgs.goodsorderid,IsCut

 

直接将查询结果拼接成字符串

select @Getvchcode,bak.dlyorder,@szDate,tmp.ptypeid,tmp.ktypeid,pgDetail,abs(stockqty - checkqty),0,
CASE WHEN ISNULL(xp.BarCode,'') = '' THEN 'ǜǒ' ELSE 'ǜǒ'+ ISNULL(xp.BarCode,'') + '-' END pgIDCode from
(SELECT b.*,c.ID FROM bakdly b,dbo.CheckedCount c WHERE b.ptypeid = c.PTypeID AND b.ktypeid = c.KTypeID AND b.GoodsBatchID = c.GoodsBatchID AND b.goodsorderID = c.goodsorderID
) bak,#PGStockTmp tmp
LEFT JOIN xw_PtypeBarCode xp ON tmp.ptypeid = xp.PTypeId
where bak.ptypeid = tmp.ptypeid and bak.vchcode = @Getvchcode AND bak.ID = tmp.checkedorderid and checkqty - stockqty > 0 ORDER BY tmp.pgDetail