SQL将多行中的单元格合并为一个单元格

Question

类似于这里的问题:
http://forums.asp.net/t/1580379.aspx/1
我正在尝试将公共(public)单元格合并到一个以逗号分隔的单元格中，但是跨内部连接。

我的 SQL 是:

SELECT DISTINCT tb_Order.OrderNumber, tb_Order.OrderId, 
  tb_Order.orderDate, tb_Order.OrderTotal, 
  tb_OrderStatus.OrderStatus, tb_Order.GroupOrderId, 
  tb_Venue.Title AS Venue
FROM tb_Order INNER JOIN tb_OrderItem ON tb_Order.OrderId = tb_OrderItem.OrderId 
  INNER JOIN tb_Show ON tb_OrderItem.ShowId = tb_Show.showId 
  INNER JOIN tb_OrderStatus ON tb_Order.OrderStatusId = tb_OrderStatus.OrderStatusID 
  INNER JOIN tb_Venue ON tb_Show.VenueId = tb_Venue.id
WHERE (tb_Order.OrderId = 705)

我需要 [venue] 以逗号分隔，例如:

"Interactive Seating Chart Advanced, Interactive Seating Chart Mode Multi-Click"

Answer 1

如果您有 SQL Server 2017 (14.x) 及更高版本，则可以使用 STRING_AGG功能。

SELECT tb_Order.OrderNumber, tb_Order.OrderId, 
  tb_Order.orderDate, tb_Order.OrderTotal, 
  tb_OrderStatus.OrderStatus, tb_Order.GroupOrderId, 
  STRING_AGG(tb_Venue.Title, ',') AS Venue
FROM tb_Order INNER JOIN tb_OrderItem ON tb_Order.OrderId = tb_OrderItem.OrderId 
  INNER JOIN tb_Show ON tb_OrderItem.ShowId = tb_Show.showId 
  INNER JOIN tb_OrderStatus ON tb_Order.OrderStatusId = tb_OrderStatus.OrderStatusID 
  INNER JOIN tb_Venue ON tb_Show.VenueId = tb_Venue.id
WHERE (tb_Order.OrderId = 705)
GROUP BY tb_Order.OrderNumber, tb_Order.OrderId, 
  tb_Order.orderDate, tb_Order.OrderTotal, 
  tb_OrderStatus.OrderStatus, tb_Order.GroupOrderId