帮我把下面这段代码的GIWarehouse字段按Rawmaterial合并成一行,通过stuff for xml path。代码如下:SELECT B.TG004 AS 'PartNumber', B.TG010 AS 'GRWarehouse', sum(B.TG011) AS 'GRQty', TEMP.* FROM (SELECT A.TA002 AS 'Order1', A.TA015 AS 'OrderQty', C.TE004 AS 'RawMaterial', C.TE008 AS 'GIWarehouse', SUM(C.TE005) AS 'GIQty' FROM MOCTA A INNER JOIN MOCTE C ON A.TA002=C.TE012 WHERE A.TA011 NOT IN ('Y','y') AND A.TA002='3180H' GROUP BY A.TA002,A.TA015,C.TE008,C.TE004) AS TEMP INNER JOIN MOCTG B ON TEMP.Order1=B.TG015 GROUP BY Order1,OrderQty,RawMaterial,GIWarehouse,GIQty,B.TG004,B.TG010
时间: 2023-11-24 16:07:00 浏览: 85
好的,以下是修改后的代码:
```
SELECT B.TG004 AS 'PartNumber', B.TG010 AS 'GRWarehouse', sum(B.TG011) AS 'GRQty',
STUFF((SELECT ', ' + TE008 FROM MOCTE WHERE TE004 = TEMP.RawMaterial
FOR XML PATH('')), 1, 2, '') AS 'GIWarehouse',
TEMP.*
FROM (SELECT A.TA002 AS 'Order1', A.TA015 AS 'OrderQty', C.TE004 AS 'RawMaterial',
SUM(C.TE005) AS 'GIQty'
FROM MOCTA A
INNER JOIN MOCTE C ON A.TA002=C.TE012
WHERE A.TA011 NOT IN ('Y','y') AND A.TA002='3180H'
GROUP BY A.TA002, A.TA015, C.TE004) AS TEMP
INNER JOIN MOCTG B ON TEMP.Order1=B.TG015
GROUP BY Order1, OrderQty, RawMaterial, TEMP.GIQty, B.TG004, B.TG010
```
这里使用了 `STUFF` 函数和 `FOR XML PATH` 子句来按 RawMaterial 合并 GIWarehouse 字段。具体来说,子查询 `SELECT ', ' + TE008 FROM MOCTE WHERE TE004 = TEMP.RawMaterial FOR XML PATH('')` 会返回 RawMaterial 对应的所有 GIWarehouse,然后 `STUFF` 函数将这些字符串拼接起来,并去掉开头的逗号和空格。