0

I want to concatenate values from 1 column and group by another. I've seen many solutions to this (using FOR XML PATH('') for example) but I've not come across one that uses more than 1 table. I need the second table to filter out some entries and I can't figure out how to do it. My data looks like this:

  SONo        PartNo
1 A04000.1    M41000M
2 A04000.1    M52000M
3 A04001.1    V31255
4 A04001.1    V32895

I want it to look like this:

  SONo          PartNo
1 A04000.1     M41000M, M52000M
2 A04001.1      V31255, V32895

My query looks like this:

SELECT     SUBSTRING(Table1.ID, 5, 10) AS SONo, Table1.PartNo
FROM         Table1 INNER JOIN
                  Table2 ON Table1.PartNo = Table2.PartNo
WHERE     (Table2.StoreNo = '13')
ORDER BY SONo

Any help would be much appreciated.

Thanks.

Quazi200
  • 13
  • 4
  • 1
    Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) – Lukasz Szozda Mar 09 '16 at 14:18
  • 1
    You search for `GROUP_CONCAT(MySQL)/LISTAGG(Oracle)/string_agg(Postgresql)` equivalent for MS SQL Server. I suggest using `STUFF + FOR XML`. More info and comparison **[Concatenating Row Values in Transact-SQL](https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/)** – Lukasz Szozda Mar 09 '16 at 14:18

1 Answers1

0

Ok so I figured it out. I saved my query as a view, then used FOR XML PATH ('') and it works a treat, if a little slow to render. Here's my new query:

SELECT t1.SalesOrderNo,
    STUFF((SELECT ', '+t2.PartNo 
             FROM dbo.SOCuttersUsed t2 
            WHERE t1.SalesOrderNo = t2.SalesOrderNo FOR XML PATH('')),1,1,'') AS PartNo
FROM dbo.SOCuttersUsed t1
GROUP BY t1.SalesOrderNo
 ORDER BY SalesOrderNo
Quazi200
  • 13
  • 4