T-SQL MS SQL Stuff. How to combine rows into single row with group by

Question

I have a SQL Query:

SELECT [B].[Id]
      ,[B].[CreateDate]
      ,[Author]
      ,[B].[WorkcenterId]
      ,[B].[AreaId]
      ,[B].[SubAreaId]
      ,[B].[Description]
      ,[PriorityId]
      ,[BreakdownStatusEnum]
      ,[ApproveDate]
      ,[StartDate]
      ,[FinishedDate]
      ,[BreakdownStartCheckListId]
      ,[ApprovedBy]
      ,[BreakdownReason]
      ,[D].[Name] AS ResponsibleDepartment
  FROM [Breakdowns_Kohl].[dbo].[Breakdowns] AS [B]
  INNER JOIN Workcenters AS [WC] ON B.WorkcenterId = WC.Id
  INNER JOIN SubAreas AS [S] ON B.SubAreaId = S.Id
  INNER JOIN Areas AS [A] ON B.AreaId = A.ID
  LEFT JOIN Relation_Department_Workcenter AS REL_DW ON B.WorkcenterId = REL_DW.WorkcenterId
  LEFT JOIN Department AS [D] ON REL_DW.DepartmentId = D.Id

which returns table:

I cannot figure out how to put it into single row with last column combined with comma separated like:

Does this answer your question? [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — HoneyBadger, Sep 28 '20 at 14:02

score 1 · Accepted Answer · edited Sep 29 '20 at 10:44

To aggregate the last column into a comma separated string called ResponsibleDepartment, something like this

SELECT [B].[Id]
      ,[B].[CreateDate]
      ,[Author]
      ,[B].[WorkcenterId]
      ,[B].[AreaId]
      ,[B].[SubAreaId]
      ,[B].[Description]
      ,[PriorityId]
      ,[BreakdownStatusEnum]
      ,[ApproveDate]
      ,[StartDate]
      ,[FinishedDate]
      ,[BreakdownStartCheckListId]
      ,[ApprovedBy]
      ,[BreakdownReason]
      ,string_agg([D].[Name] , ',') within group (order by [B].id) ResponsibleDepartment
  FROM [Breakdowns_Kohl].[dbo].[Breakdowns] AS [B]
  INNER JOIN Workcenters AS [WC] ON B.WorkcenterId = WC.Id
  INNER JOIN SubAreas AS [S] ON B.SubAreaId = S.Id
  INNER JOIN Areas AS [A] ON B.AreaId = A.ID
  LEFT JOIN Relation_Department_Workcenter AS REL_DW ON B.WorkcenterId = REL_DW.WorkcenterId
  LEFT JOIN Department AS [D] ON REL_DW.DepartmentId = D.Id

group by [B].[Id],
      [B].[CreateDate]
      ,[Author]
      ,[B].[WorkcenterId]
      ,[B].[AreaId]
      ,[B].[SubAreaId]
      ,[B].[Description]
      ,[PriorityId]
      ,[BreakdownStatusEnum]
      ,[ApproveDate]
      ,[StartDate]
      ,[FinishedDate]
      ,[BreakdownStartCheckListId]
      ,[ApprovedBy]
      ,[BreakdownReason]

score 0 · Answer 2 · answered Sep 28 '20 at 15:59

0

if you are using SQL server 2017 or above as it mentioned you can easily use String_AGG aggregate function , if not you can use stuff function and xml like in this question :

string_agg for sql server pre 2017

answered Sep 28 '20 at 15:59

eshirvana

23,227
3
22
38

T-SQL MS SQL Stuff. How to combine rows into single row with group by

2 Answers2