Combine SQL rows into one column based off two other columns-CodePudding

I have this SQL statement:

SELECT
    bs.ITEMNAME AS Item,
    bom.ITEM AS PartNumber,
    (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'

It returns a table like:

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

But I am looking to have something like this:

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

How do I change my SQL statement to accomplish this?

CodePudding user response：

A very simple example of this is:

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,'')

output: a,b,c

CodePudding user response：

To use XML like this you also need the STUFF function to concatenate the strings for you. Something like this (but may need tweaking):

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'