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 :stackoverflow.com/questions/451415/… — TheGameiswar
– TheGameiswar, Commented Aug 12, 2017 at 5:19
Possible duplicate of Simulating group_concat MySQL function in Microsoft SQL Server 2005? — TheGameiswar
– TheGameiswar, Commented Aug 12, 2017 at 5: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
– c_174, Commented Aug 12, 2017 at 5:32

Jonathan Willcock · Accepted Answer · 2017-08-13 05:57:30Z

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, 2017 at 5:57

answered Aug 12, 2017 at 5:49

Jonathan Willcock

5,4303 gold badges25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

c_174 Over a year ago

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 Over a year ago

...If you can please help getting my code corrected

Jonathan Willcock Over a year ago

Please edit your question with the new attempt and tell us the error.

Jonathan Willcock Over a year ago

Is EmailAddress in OrderDetails?

c_174 Over a year ago

no it is in customers table. and product code is in order details table

|

Collectives™ on Stack Overflow

I want to do group_concat in SQL Server

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related