SQL Merge Cells from many rows into a single cell

Question

Similar to the question here: http://forums.asp.net/t/1580379.aspx/1 I'm trying to merge common cells into a single comma-delimited cell, however across an inner join.

My SQL is:

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)

enter image description here

I need the [venue] to be comma-delimited like:

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

Take a look at http://stackoverflow.com/questions/905818/tsql-comma-separation/913023 — mikurski, Apr 27 '12 at 00:59
possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) — Andriy M, Apr 27 '12 at 09:48
Andriy - not a duplicate as I'm tring to do this with a join. — aron, Apr 27 '12 at 17:49

score 1 · Answer 1 · answered Oct 22 '20 at 15:10

If you have SQL Server 2017 (14.x) and later, you can use the STRING_AGG function.

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

score 0 · Answer 2 · answered Apr 27 '12 at 01:36

One way to do this is by using a side effect of row processing order to populate a variable iteratively, like so. The downside of this is that it won't work in a simple query context and it's not the most efficient solution.

    DECLARE @venues varchar(max)
      SET @venues = '' 

    SELECT @venues = 
      CASE WHEN @venues = '' THEN v.Title 
      ELSE @venues + ',' + v.Title END
    FROM tb_Venue v 

    SELECT @venues

A second way to do this is with STUFF and SQL Server XML extensions, like so:

        SELECT DISTINCT v.Title,
        (STUFF(
        (SELECT ',' + v2.Title
         FROM tb_Venue v2
        -- uncomment this line if you are going 
        -- to aggregate only by something in the outer query
        -- WHERE v2.GroupKey = v.GroupKey  
         ORDER BY v2.Title
         FOR XML PATH(''), TYPE, ROOT
        ).value('root[1]','varchar(max)'),1,1,'')) as Aggregation
        FROM tb_Venue v

A CLR-based solution is usually the most performant for this use case, and one of those is described here along with a boatload of other less ideal solutions...

Your problem is not one rooted in set logic so there isn't a clean SQL solution...

SQL Merge Cells from many rows into a single cell

2 Answers2