Combine Row Results SQL

Question

I looked up several answers that didn't seem to quite work for me, or I used them incorrectly with my query.

Current query:

SELECT
    PartNo,
    SubPartNo
FROM
    Materials

Current Results:

+--------+-----------+
| PartNo | SubPartNo |
+--------+-----------+
|    541 |      5451 |
|    545 |      5451 |
+--------+-----------+

Desired Results:

+----------+-----------+
|  PartNo  | SubPartNo |
+----------+-----------+
| 541, 545 |      5451 |
+----------+-----------+

The result doesn't necessarily need the comma. I am sure this is an easy fix but my limited know-how is slowing me down.

Google: "SQL Server string aggregation". You may decide you can live with the original format. — Gordon Linoff, Sep 25 '17 at 19:20
Possible duplicate of [Concatenate grouped rows](https://stackoverflow.com/questions/9139472/concatenate-grouped-rows) — Radim Bača, Sep 25 '17 at 19:20
use `FOR XML XPATH`: https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ — Radim Bača, Sep 25 '17 at 19:21
Thank you, that link really helped. I saw the XML path on one post but without explanation I wasn't really following it. It worked like a charm. — RBC Kyle Bullard, Sep 25 '17 at 19:26

score 0 · Accepted Answer · answered Sep 25 '17 at 19:27

After comments directing me to https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/ my question was answered.

SELECT DISTINCT
    matl1.SubPartNo,
    STUFF((
            SELECT ', ' + matl2.PartNo
            FROM Materials as matl2
            WHERE matl2.SubPartNo = matl1.SubPartNo
            FOR XML PATH('')
            ),1,1,'')
FROM
    Materials as matl1

Combine Row Results SQL

1 Answers1