CODEWITHSUNDEEP
sqlt-sqlfor-xml-path
user3580480
user3580480

Reputation: 482

Group by for XML Path ibn SQL

I am trying to concatenate some values from a column into a single field.

So far I have the below code.

SELECT DISTINCT [customer id]
      ,[customer name]
      ,STUFF( (SELECT ',' + [description] 
                             FROM [Invoicing].[dbo].[CurrentBillMaster] 
                             ORDER BY [description]
                             FOR XML PATH('')),
                            1, 1, '')
      ,[id]
      ,[Section]
      ,[customerpo]
  FROM [Invoicing].[dbo].[CurrentBillMaster]
  GROUP BY [customer id], [customer name], [description],[qty],
           [identifier],[FromDate],[ToDate],[id],[Section],[customerpo]

The code largely works, however I want the concatenated description, just to show descriptions for that unique [customer id]

Any help greatly appreciated

Upvotes: 2

Views: 1936

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176314

You need to correlate subquery:

SELECT  [customer id]
      ,[customer name]
      ,STUFF( (SELECT ',' + [description] 
                             FROM [Invoicing].[dbo].[CurrentBillMaster] t
                             WHERE  t.Customer_id = c.customer_id  -- here
                             ORDER BY [description]
                             FOR XML PATH('')),
                            1, 1, '')
      ,[id]
      ,[Section]
      ,[customerpo]
  FROM [Invoicing].[dbo].[CurrentBillMaster] c
  GROUP BY [customer id], [customer name], [description],[qty],
           [identifier],[FromDate],[ToDate],[id],[Section],[customerpo]

Upvotes: 6

Related Questions