I want to do group_concat in SQL Server

Question

I know group_concat doesn't work in SQL Server 2008, but I want to do group_concat.

My sample data looks like this:

email address         | product code   
----------------------+---------------
garry123@gmail.com    | A123A  
garry123@gmail.com    | AB263    
ada121@hotmail.com    | 45632A   
ada121@hotmail.com    | 78YU
garry123@gmail.com    | 6543D 

and I want this result:

garry123@gmail.com | A123A,AB263,6543D 
ada121@hotmail.com | 45632A,78YU

Code I have tried:

SELECT
    c.EmailAddress,
    od.concat(productcode) as Product_SKU
FROM
    OrderDetails od
JOIN 
    Orders o ON od.OrderID = o.OrderID 
JOIN 
    Customers c ON c.CustomerID = o.CustomerID
WHERE
    o.OrderDate BETWEEN 01/01/2016 AND GETDATE()
GROUP BY 
    c.EmailAddress

I get an error:

Cannot find either column "od" or the user-defined function or aggregate "od.concat", or the name is ambiguous.

But this is not working. Can anyone please tell me correct way of doing this?

Code I am trying after editing:

SELECT
    c.EmailAddress,
    productcode = STUFF((SELECT ',' + od.productcode
                         FROM Orderdetails od
                         WHERE c.EmailAddress = od.EmailAddress
                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM
    OrderDetails od
JOIN 
    Orders o ON od.OrderID = o.OrderID 
JOIN 
    Customers c ON c.CustomerID = o.CustomerID
WHERE
    o.OrderDate BETWEEN 01/01/2016 AND GETDATE()

Now I'm getting this error:

Invalid column name 'EmailAddress'.

Answer 1

To give you an illustration of joining (in this case a self join, but all joins work) and using STUFF for this. Notice the WHERE clause inside the STUFF. This is what links the record to the correct values.

declare @test table
(
email varchar(50),
address varchar(50)
)

insert into @test VALUES
('garry123@gmail.com','A123A'),  
('garry123@gmail.com','AB263'),   
('ada121@hotmail.com','45632A'),   
('ada121@hotmail.com','78YU'),
('garry123@gmail.com','6543D')

SELECT DISTINCT 
       email,
       Stuff((SELECT ', ' + address  
              FROM   @test t2 
              WHERE  t2.email  = t1.email  
              FOR XML PATH('')), 1, 2, '') Address
FROM   @test t1  

Edit

OK so what you want (what you really, really want) is:

declare @customers table
(
emailaddress varchar(50),
customerid int
)

insert into @customers VALUES
('garry123@gmail.com',1),  
('ada121@hotmail.com',2)   

declare @orders table
(
orderid int,
customerid int,
orderdate date
)

insert into @orders VALUES
(1, 1, '2017-06-02'),
(2, 1, '2017-06-05'),
(3, 1, '2017-07-13'),
(4, 2, '2017-06-13')

declare @orderdetails table
(
id int,
orderid int,
productcode varchar(10)
)

insert into @orderdetails VALUES
(1, 1, 'apple pie'),
(2, 1, 'bread'),
(3, 2, 'custard'),
(4, 2, 'orange'),
(5, 3, 'orange'),
(6, 4, 'orange')

SELECT DISTINCT c.EmailAddress, productcode=
STUFF((SELECT ',' + odc.productcode FROM 
(SELECT DISTINCT emailaddress,productcode FROM 
@orders o2 inner join @orderdetails od2 on
o2.orderid = od2.orderid
inner join @customers c2 ON c2.customerid = o2.customerid) odc 
WHERE odc.emailaddress=c.emailaddress 
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM @OrderDetails od 
JOIN @Orders o ON od.OrderID = o.OrderID 
JOIN @Customers c ON c.CustomerID=o.CustomerID 
WHERE o.OrderDate BETWEEN '2016-01-01' AND getdate()

Notice the change here. The SELECT in the STUFF is now from a sub-query, so that you can group by EmailAddress.

Additional explanation

Your aim is to have a concatenation of product codes grouped by customer (represented by email address). The problem is that product codes are in the orderdetails table and emailaddress is in the customer table, but there is no field which links the two. The customer table has a one to many relationship with the orders table and the orders table has a one to many relationship with the orderdetails table. That is one level of abstraction too many. So we need to give the database a helping hand by providing a direct link between productcode and emailaddress. This we do via the Sub-query. I hope that this makes it clearer for you.