1

I am fetching some results from three different tables Orders, OrderDetails and Customers. I am trying to combine two fields OrderDetails.ProductCode and OrderDetails.Quantity into one and group the results by OrderID.

What I am getting is

OrderID ProductCode Quantity Email
1       A           2        john@gmail.com
1       D           1        john@gmail.com
2       E           5        warren@gmail.com
3       B           3        david@gmail.com
3       F           1        david@gmail.com

This is what I want

OrderID ProductCode_Quantity Email
1       A_2, D_1             john@gmail.com
2       E_5,                 warren@gmail.com
3       B_3, F_1             david@gmail.com

My current query is

SELECT Orders.OrderID, OrderDetails.ProductCode, OrderDetails.Quantity, Customers.EmailAddress
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
AZee
  • 520
  • 1
  • 6
  • 22
  • What DBMS are you using? MySQL? I would edit your question to include the proper tag, as this is a problem that may vary by DBMS. – AdamMc331 Feb 17 '15 at 16:19
  • I believe its MS SQL as its an ASP hosting. Actually I don't have direct access to DBMS I can only run queries through a query bank available in a CMS I am working on. Thank you – AZee Feb 17 '15 at 16:26
  • Ah. If it was MySQL you can use something like `GROUP_CONCAT()` but I'm not sure about MSSQL. Maybe [this question](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) will help you? – AdamMc331 Feb 17 '15 at 16:29
  • thank you but it looks something different – AZee Feb 17 '15 at 20:05

0 Answers0