SQL get multiple results into one cell?

Question

So I'm running a report to see how many times 1 customer has placed an order with us. I have most of my data their email, how many times they have bought , their name, last order date and last OrderID.

What I can't figure out how to get is to get the productCode from items from that order.

The items are stored in a seperate table called Lines and are linked to the main order by OrderID.

Is there a way that if there are multplie lines per order to put ProductCodes from those lines as a comma seperated value?

Something like that

BuyerEmail | HowMany | Name | ProductCodes
--------------------------------------------
budyn@bud  | 12      | Bud  | 1231, 123,

This query will explain it better...:

SELECT TOP 1000 
o.BuyerEMail
,COUNT(*) HowMany
,o.Name
,o2.OrderID
FROM Orders o
JOIN 
(
 SELECT 
 BuyerEmail
 ,MAX(OrderDate) Latest 
 FROM Orders 
 GROUP BY BuyerEmail
) l
ON o.BuyerEmail = l.BuyerEmail

JOIN Orders o2
ON l.BuyerEmail = o2.BuyerEmail
AND l.OrderDate = o2.OrderDate

WHERE Pay != 'PayPal'

GROUP BY 
o.BuyerEmail
,o.Name
,l.Latest
ORDER BY 
COUNT(*) DESC

Is it possible to concatenate cells? I just don't know where to get started here.

Thank you

Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Tab Alleman, May 19 '16 at 16:23
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — Morpheus, May 19 '16 at 16:35

score 1 · Answer 1 · answered May 19 '16 at 17:04

Of course it is possible, you need to do something like this:

select NId_Lista_Surtido,
    stuff((select cast(',' as varchar(max))+ cast(nid_detalle_surtido as varchar(max)) <<<--- this is the value being appended
    from CES_DLista_Surtido dsurtido <-- this is the detail table
    where dsurtido.nid_lista_surtido=tsurtido.nid_lista_surtido <-- this is the condition 
    for xml path('')),1,1,'') as prueba <-- this is how the col name.
    from  CES_TLista_Surtido tsurtido <-- this is the main table

and this is the ouptput

NId_Lista_Surtido prueba 1 95854,95855,95856,95857,95858,95859,95860,95861,95862,95863,95864,95865,95866,95867,95868,95869,95870,95871,95872,95873,95874,95875,95876

this is an working example with my data, stuff is the function you need to use, if you have doubts ask me.

SQL get multiple results into one cell?

1 Answers1