根据其他两个列将SQL行组合成一个列

Question

我有这样的SQL语句:

SELECT
bs.ITEMNAME AS Item,
bom.ITEM AS PartNumber,
STUFF(SELECT ed.NOTE AS [text()] 
WHERE bom.ITEM = ed.itemid 
FOR XML PATH ('')) AS Description
FROM 
vwAXBOM bom
LEFT JOIN 
S2BOMSTR bs ON bom.POSITION = bs.ITEMNUM
LEFT JOIN 
vwExtendedDescriptionMFG ed ON bom.ITEM = ed.itemid
WHERE 
bom.BOMITEM = @partNum 
AND bom.POSITION LIKE 'S%'  
AND bom.POSITION != 'S76'

返回如下表:

| Item | PartNumber | Description
+------+------------+--------------
| Film |  1234      | Some thing
| Film |  1234      | Other thing
| Flap |  5678      | Another thing
| Flap |  5678      | Final Thing

但我希望有这样的东西:

| Item | PartNumber | Description
+------+------------+---------------------------
| Film |   1234     | Some thing, Other thing
| Flap |   5678     | Another thing, Final thing

我如何改变我的SQL语句来完成这个?

Answer 1

这个问题的解决方案是为主查询创建一个临时表。然后利用FOR XML PATH()函数从临时表中提取适当的数据。

SELECT
bs.ITEMNAME AS Item
,bom.ITEM AS PartNumber
INTO #tempBomList
FROM vwAXBOM bom
LEFT JOIN S2BOMSTR bs 
ON bom.POSITION = bs.ITEMNUM
LEFT JOIN vwExtendedDescriptionMFG ed
ON bom.ITEM = ed.itemid
WHERE bom.BOMITEM = @partNum AND bom.POSITION LIKE 'S%'  AND bom.POSITION <> 'S76' 
GROUP BY bs.ITEMNAME
,bom.ITEM

SELECT tb.Item
,tb.PartNumber
,(SELECT NOTE +  ', '
FROM vwExtendedDescriptionMFG 
WHERE ITEMID = tb.PartNumber
FOR XML PATH('')) AS Description
FROM #tempBomList tb

Answer 2

一个非常简单的例子是:

WITH cte as (
SELECT 'a' as A
union all
select 'b'
union all
select 'c')
select stuff((select ', '+A from cte for xml path('')),1,2,'')

输出:a,b,c

Answer 3

要像这样使用XML，还需要STUFF函数为您连接字符串。类似这样的内容(但可能需要调整):

SELECT
bs.ITEMNAME AS Item,
bom.ITEM AS PartNumber,
STUFF((SELECT ',' + ed.NOTE AS [text()] 
WHERE bom.ITEM = ed.itemid 
FOR XML PATH ('') ),1,1,'') AS [Description]
FROM 
vwAXBOM bom
LEFT JOIN 
S2BOMSTR bs ON bom.POSITION = bs.ITEMNUM
LEFT JOIN 
vwExtendedDescriptionMFG ed ON bom.ITEM = ed.itemid
WHERE 
bom.BOMITEM = @partNum 
AND bom.POSITION LIKE 'S%'  
AND bom.POSITION <> 'S76'