我在查询SQL Server 2005数据库时遇到了一个简单的问题。我有两个表,分别是客户 (Customer) 和产品 (Products)(1->M)。一个客户最多有两个产品。我希望输出结果如下:
CustomerName, Product1Name, Product2Name ...
而不是
CustomerName, ProductName ...
请问是否有人能帮助我?
谢谢!
我在查询SQL Server 2005数据库时遇到了一个简单的问题。我有两个表,分别是客户 (Customer) 和产品 (Products)(1->M)。一个客户最多有两个产品。我希望输出结果如下:
CustomerName, Product1Name, Product2Name ...
而不是
CustomerName, ProductName ...
请问是否有人能帮助我?
谢谢!
像其他人所说的那样,您可以使用PIVOT和UNPIVOT运算符。不幸的是,PIVOT和UNPIVOT的问题之一是您需要事先知道要旋转的值,否则需要使用动态SQL。
在您的情况下,似乎需要使用动态SQL。为了使其正常工作,您需要提取查询中使用的产品列表。如果您正在使用AdventureWorks数据库,则代码如下:
USE AdventureWorks;
GO
DECLARE @columns NVARCHAR(MAX);
SELECT x.ProductName
INTO #products
FROM (SELECT p.[Name] AS ProductName
FROM Purchasing.Vendor AS v
INNER JOIN Purchasing.PurchaseOrderHeader AS poh ON v.VendorID = poh.VendorID
INNER JOIN Purchasing.PurchaseOrderDetail AS pod ON poh.PurchaseOrderID = pod.PurchaseOrderID
INNER JOIN Production.Product AS p ON pod.ProductID = p.ProductID
GROUP BY p.[Name]) AS x;
SELECT @columns = STUFF(
(SELECT ', ' + QUOTENAME(ProductName, '[') AS [text()]
FROM #products FOR XML PATH ('')
), 1, 1, '');
SELECT @columns;
现在您有了列,可以使用动态查询获取需要进行数据透视的所有内容:
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT CustomerName, ' + @columns + '
FROM (
// your query goes here
) AS source
PIVOT (SUM(order_count) FOR product_name IN (' + @columns + ') AS p';
EXEC sp_executesql @sql
当然,如果您需要确保获取合适的值,您可能需要复制正在使用的逻辑来构建@columns,并创建一个@coalesceColumns变量,该变量将保存COALESCE(col_name, 0)代码,以便在查询中需要这样的内容。
正如其他人所提到的,SQL 2005具有PIVOT函数,这可能是最适合一般用途的。然而,在某些情况下,您可以简单地执行类似于以下操作。
Select
Customer,
Sum(Case When Product = 'Foo' Then 1 Else 0 End) Foo_Count,
Sum(Case When Product = 'Bar' Then 1 Else 0 End) Bar_Count
From Customers_Products
Group By Customer