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:

Answer 1

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]

Answer 2

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