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   
----------------------+---------------
[email protected]    | A123A  
[email protected]    | AB263    
[email protected]    | 45632A   
[email protected]    | 78YU
[email protected]    | 6543D

and I want this result:

[email protected] | A123A,AB263,6543D 
[email protected] | 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'.

look out this answers :https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — TheGameiswar, Aug 12 '17 at 05:19
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — TheGameiswar, Aug 12 '17 at 05:19
@TheGameiswar..I looked into solution but how will i apply that to my problem? what is table_name,column_name in that solution?? and i have 3 tables to join. I would appreciate if you can help me more — c_174, Aug 12 '17 at 05:32

Jonathan Willcock · Accepted Answer · 2017-08-13T05:57:30.840

2

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
('[email protected]','A123A'),  
('[email protected]','AB263'),   
('[email protected]','45632A'),   
('[email protected]','78YU'),
('[email protected]','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
('[email protected]',1),  
('[email protected]',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.

edited Aug 13 '17 at 05:57

answered Aug 12 '17 at 05:49

Jonathan Willcock

5,012
3
20
31

I am trying this way: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() but getting error message – c_174 Aug 12 '17 at 05:52
...If you can please help getting my code corrected – c_174 Aug 12 '17 at 05:57
Please edit your question with the new attempt and tell us the error. – Jonathan Willcock Aug 12 '17 at 06:00
Is EmailAddress in OrderDetails? – Jonathan Willcock Aug 12 '17 at 06:09
no it is in customers table. and product code is in order details table – c_174 Aug 12 '17 at 06:14
Then you need WHERE od.OrderID = o.OrderID (not EmailAddress) – Jonathan Willcock Aug 12 '17 at 06:18
..it passed that error but query is still not running and giving opening recordset and nothing is happening..can you please check if i need everything in last from clause? – c_174 Aug 12 '17 at 06:22
Try BETWEEN '2016-01-01' AND – Jonathan Willcock Aug 12 '17 at 06:23
..It is working but not giving right results. In my results i have 1000 of email addresses and let's say [email protected] bought 2 products in june and then again 4 products in july. so the result is not grouping two months product together. It is showing them separately. Is there a way to group them? – c_174 Aug 12 '17 at 06:40
See my edit - I revised my answer to group orders together by customer – Jonathan Willcock Aug 12 '17 at 07:27
..Thanku so much..It is working the way i want..but can you explain me in detail why another select is needed? – c_174 Aug 12 '17 at 22:38
..Thanku so much...:) – c_174 Aug 13 '17 at 23:54

I want to do group_concat in SQL Server

1 Answers1

Linked

Related