如何将行转换为列 [英] How to transform rows to columns

问题描述

我在查询 SQL Server 2005 数据库时遇到了一个简单的问题.我有名为 Customer 和 Products (1->M) 的表.一位客户最多拥有 2 件产品.而不是输出为

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

客户名称、产品名称...

CustomerName, ProductName ...

我喜欢输出为

客户名称、产品 1 名称、产品 2 名称 ...

CustomerName, Product1Name, Product2Name ...

有人可以帮我吗?

谢谢！

推荐答案

正如其他人所说，您可以使用 PIVOT 和 UNPIVOT 运算符.不幸的是，PIVOT 和 UNPIVOT 的问题之一是您需要提前知道将要透视的值，否则使用动态 SQL.

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.

听起来，就您而言，您将需要使用动态 SQL.为了使这项工作正常进行，您需要提取查询中使用的产品列表.如果您使用的是 AdventureWorks 数据库，您的代码将如下所示:

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:

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

当然，如果您需要确保获得合适的值，您可能需要复制用于构建@columns 的逻辑并创建一个@coalesceColumns 变量，该变量将保存代码为 COALESCE(col_name, 0)如果您在查询中需要此类内容.

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.

这篇关于如何将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！