我想在SQL Server中做group_concat [英] I want to do group_concat in SQL Server

问题描述

我知道group_concat在SQL Server 2008中不起作用，但是我想执行group_concat.

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

我的样本数据如下:

email address         | product code   
----------------------+---------------
[e-mail]    | A123A  
[e-mail]    | AB263    
[e-mail]    | 45632A   
[e-mail]    | 78YU
[e-mail]    | 6543D 

我想要这个结果:

[e-mail] | A123A,AB263,6543D 
[e-mail] | 45632A,78YU

我尝试过的代码:

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

我得到一个错误:

找不到列"od"或用户定义的函数或聚合"od.concat"，或者名称不明确.

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()

现在我收到此错误:

无效的列名"EmailAddress".

Invalid column name 'EmailAddress'.

推荐答案

为您举例说明联接(在这种情况下为自我联接，但所有联接均有效)并为此使用STUFF.注意STUFF中的WHERE子句.这就是将记录链接到正确值的原因.

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
('[e-mail]','A123A'),  
('[e-mail]','AB263'),   
('[e-mail]','45632A'),   
('[e-mail]','78YU'),
('[e-mail]','6543D')

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

编辑

好的，所以您想要的(您真正想要的)是:

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

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

insert into @customers VALUES
('[e-mail]',1),  
('[e-mail]',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()

在此处通知更改.现在，STUFF中的SELECT来自一个子查询，因此您可以按EmailAddress进行分组.

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

其他说明

您的目标是将按客户分组的产品代码(以电子邮件地址表示)串联起来.问题是产品代码在orderdetails表中，而emailaddress在customer表中，但是没有将两者链接的字段.客户表与订单表具有一对多关系，而订单表与订单细节表具有一对多关系.那是抽象层次太多了.因此，我们需要通过提供产品代码和电子邮件地址之间的直接链接来帮助数据库.我们通过子查询来完成.我希望这可以使您更清楚.

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.

这篇关于我想在SQL Server中做group_concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！