-1

On MS SQL Server 2008, how do I make a select statement, that joins two tables, and return the content of table 2 as a comma-separated list?

Example: Table 1 contains all my orders, and table 2 my order lines. I want to select all fields from orders, as well as a comma separated list of all fields from the corresponding order lines, with each order line separated by semicolon (or something else).

So something like this:

| Orders.custno | Orders.name | Orders.email     | Orders.phone | Order lines                                                         |
|---------------|-------------|------------------|--------------|---------------------------------------------------------------------|
| 123456        | Dave        | dave@example.com | 1234567890   | “112233”,”Running shoes”,”495.00”;”223344”,”Roller blades”,”699.00” |

Hope this makes sense.

Thanks.

Ploughansen
  • 303
  • 1
  • 5
  • 14
  • 1
    Without sample data and desired results no one can help you. In the meantime, you can help yourself and google "SQL Server string aggregation". – Gordon Linoff May 27 '18 at 22:25
  • Thank you. I was pretty sure I wasn't the only one who ever encountered this issue, so of course the question has been asked before. However, sometimes it can be hard to find a solution on google, when you're not exactly sure how to formulate the question. – Ploughansen May 28 '18 at 06:43

1 Answers1

1

If I understand your question correctly then below is the solution SQLFiddler

SELECT p.productId,p.ProductName,tmp.List_output
FROM product p
LEFT OUTER JOIN 
(
SELECT  ProductId
       ,STUFF((SELECT ', ' + CAST(ProductLine AS VARCHAR(10)) [text()]
         FROM ProductLine 
         WHERE ProductId = t.ProductId
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM ProductLine t
GROUP BY ProductId) tmp ON tmp.ProductId = p.ProductId
Rajat Jaiswal
  • 645
  • 4
  • 15