0

I have a SQL Server database of orders I'm struggling with. For a normal order a single table provides the following results:

Orders:

     ID              Customer        Shipdate         Order ID
-----------------------------------------------------------------
      1                Tom           2015-01-01           100
      2                Bob           2014-03-20           200

At some point they needed orders that were placed by more than one customer. So they created a row for each customer and split the record over multiple rows.

Orders:

     ID              Customer        Shipdate         Order ID
-----------------------------------------------------------------
      1                Tom           2015-01-01           100
      2                Bob           2014-03-20           200
      3                John                     
      4                Dan                     
      5                              2014-05-10           300

So there is another table I can join on to make sense of this which relates the three rows which are actually one order.

Joint.Orders:

     ID              Related ID
-----------------------------------------------------------------
      5                  3 
      5                  4

I'm a little new to SQL and while I can join on the other table and filter to only get the data relating to Order ID 300, but what I'd really like is to concatenate the customers, but after searching for a while I can't see how to do this. What'd I'd really like to achieve is this as an output:

 ID              Customer        Shipdate         Order ID
----------------------------------------------------------------
  1                Tom           2015-01-01           100
  2                Bob           2014-03-20           200
  5                John, Dan     2014-05-10           300
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tarka the otter
  • 1
  • Poor you, have to deal with crappy table design. You need to do a JOIN with a GROUP BY, and some kind of GROUP_CONCAT (or whatever MS SQL Server calls it.) – jarlh Apr 16 '15 at 15:29
  • As mentioned by @jarlh you need the SQL Server equivalent of MySQL GROUP_CONCAT is what you require. Unfortunately, that function doesn't exist. However, this has been asked before, see this post: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server – BeaglesEnd Apr 16 '15 at 16:33

1 Answers1

0

You should consider changing the schema first. The below query might help you get a feel of how it can be done with your current design.

Select * From Orders Where IsNull(Customer, '') <> '' Union All Select ID, Customer = (Select Customer + ',' From Orders OI Where OI.ID (Select RelatedID from JointOrders JO Where JO.ID = O.ID) ,ShipDate, OrderID From Orders O Where IsNull(O.Customer, '') = ''

XtremeBytes
  • 1,469
  • 8
  • 12