如何将行转换为列

I have a simple problem when querying the SQL Server 2005 database. I have tables called Customer and Products (1->M). One customer has most 2 products. Instead of output as

查询SQL Server 2005数据库时遇到一个简单的问题。我有一个名为Customer and Products（1-> M）的表。一个客户拥有最多2个产品。而不是输出

CustomerName, ProductName ...

客户名称，产品名称......

I like to output as

我喜欢输出为

CustomerName, Product1Name, Product2Name ...

CustomerName，Product1Name，Product2Name ...

Could anybody help me?

有人能帮助我吗？

Thanks!

谢谢！

4 个解决方案

#1

Like others have said, you can use the PIVOT and UNPIVOT operators. Unfortunately, one of the problems with both PIVOT and UNPIVOT are that you need to know the values you will be pivoting on in advance or else use dynamic SQL.

像其他人所说，你可以使用PIVOT和UNPIVOT运算符。不幸的是，PIVOT和UNPIVOT的一个问题是你需要事先知道要转动的值，否则就使用动态SQL。

It sounds like, in your case, you're going to need to use dynamic SQL. To get this working well you'll need to pull a list of the products being used in your query. If you were using the AdventureWorks database, your code would look like this:

听起来，在您的情况下，您将需要使用动态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;

Now that you have your columns, you can pull everything that you need pivot on with a dynamic query:

现在您已经拥有了列，您可以使用动态查询提取所需的所有内容：

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

Of course, if you need to make sure you get decent values, you may have to duplicate the logic you're using to build @columns and create an @coalesceColumns variable that will hold the code to COALESCE(col_name, 0) if you need that sort of thing in your query.

当然，如果你需要确保得到合适的值，你可能必须复制你用来构建@columns的逻辑并创建一个@coalesceColumns变量，如果你需要，它将把代码保存到COALESCE（col_name，0）你的查询中的那种东西。

#2

Here two link about pivot:

这里有两个关于pivot的链接：

http://www.tsqltutorials.com/pivot.php

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

I solve my problem with pivot ;)

我用pivot解决了我的问题;）

#3

in sql2005, there are functions called "PIVOT" and "UNPIVOT" which can be used to transform between rows and columns.

在sql2005中，有一些名为“PIVOT”和“UNPIVOT”的函数可用于在行和列之间进行转换。

Hope that could help you.

希望能帮助你。

#4

As others have mentioned, SQL 2005 has the PIVOT function which is probably the best for general use. In some cases, however, you can simply do something like this.

正如其他人所提到的，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

#1