将行设置为列的 SQL 查询

Question

由于我使用的是 SQL Server 2000 而没有 SQL Server 2005 的数据透视表，所以我需要一些帮助来创建查询。

下面是一些示例数据。

TBLProduct
ProductID   ProductName
1           Jacket
2           Blazer
3           Chaleco

TBLFactors
FactorID    FactorName
1           Length
2           Threading
3           Wool
4       Cotton

TBLFactorInspect
ID  ProductID   FactorID    FactorValue
1   1           1           5.00
2   1           2           5.55
3   2           2           6.33
4   2           3           3.66
5   2           4           1.05

我需要有关将数据输出到此的查询的帮助:

ProductID   ProductName Length  Threading   Wool    Cotton
1           Jacket      5.00    5.55        -       -
2           Blazer      -       6.33        3.66    1.05
3           Chaleco     -       -           -       -

我想我还需要另一个查询，例如，仅查询 ProductID = 1，结果数据将返回因子作为已定义值的列，即:

ProductID   ProductName Length  Threading
1           Jacket      5.00    5.55

非常感谢任何建议。谢谢。

更新:如果存储过程是实现此目的的最佳答案，那么我对此没有任何问题，因为我似乎认为 SQL Server 2K 在创建数据透视表方面的限制是这里的主要障碍。

我正在尝试在存储过程解决方案中处理动态 SQL，但我在动态列 - 值映射方面没有取得太大进展。

Answer 1

我手边没有 SQL Server 2000 实例可供尝试，但看看这是否能让您有所收获。我对有条件地包含列的要求感到有点惊讶 - 希望应用程序能够处理列突然出现和消失的情况，具体取决于 TBLFactorInspect 表中的参数和不断变化的数据。

USE [tempdb];
GO
SET NOCOUNT ON;
GO

设置:

CREATE TABLE dbo.TBLProduct
(
    ProductID INT PRIMARY KEY, ProductName NVARCHAR(50)
);

INSERT dbo.TBLProduct(ProductID, ProductName)
SELECT 1, 'Jacket'
UNION SELECT 2, 'Blazer'
UNION SELECT 3, 'Chaleco';

CREATE TABLE dbo.TBLFactors
(
    FactorID INT PRIMARY KEY, FactorName NVARCHAR(50)
);

INSERT dbo.TBLFactors(FactorID, FactorName)
SELECT 1, 'Length'
UNION SELECT 2, 'Threading'
UNION SELECT 3, 'Wool'
UNION SELECT 4, 'Cotton';

CREATE TABLE dbo.TBLFactorInspect
(
    ID INT PRIMARY KEY, ProductID INT,
    FactorID INT, FactorValue DECIMAL(5,2)
);

INSERT dbo.TBLFactorInspect(ID, ProductID, FactorID, FactorValue)
SELECT 1,1,1,5.00 UNION SELECT 2,1,2,5.55
UNION SELECT 3,2,2,6.33 UNION SELECT 4,2,3,3.66
UNION SELECT 5,2,4,1.05;
GO

现在是一些代码:

CREATE PROCEDURE dbo.GetProductPivot
    @ProductID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(4000);
    SET @sql = N'';

    SELECT @sql = @sql + N'
        ' + QUOTENAME(FactorName) 
        + ' = MAX(CASE WHEN d.FactorID = ' 
        + RTRIM(FactorID) + ' THEN d.FactorValue END),'
    FROM
    (
        SELECT f.FactorID, f.FactorName
        FROM dbo.TBLFactorInspect AS d
        INNER JOIN dbo.TBLFactors AS f
        ON f.FactorID = d.FactorID
        WHERE (d.ProductID = @ProductID OR @ProductID IS NULL)
        GROUP BY f.FactorID, f.FactorName
    ) AS f
    ORDER BY f.FactorID;

    IF @sql = N''
    BEGIN
        SELECT ProductID, ProductName, 
            Result = 'No data in TBLFactorInspect'
            FROM dbo.TBLProduct
            WHERE ProductID = COALESCE(@ProductID, ProductID);
    END
    ELSE
    BEGIN
        SELECT @sql = N'SELECT p.ProductID, p.ProductName, ' + 
            LEFT(@sql, LEN(@sql)-1) + '
                FROM dbo.TBLProduct AS p
                LEFT OUTER JOIN dbo.TBLFactorInspect AS d
                ON p.ProductID = d.ProductID
                ' + CASE WHEN @ProductID IS NOT NULL THEN 
                ' WHERE p.ProductID = ' + RTRIM(@ProductID) ELSE '' END + '
                GROUP BY p.ProductID, p.ProductName
                ORDER BY p.ProductID;';

        EXEC sp_executeSQL @sql;
    END
END
GO

一些证明它可以在没有参数或特定产品 ID 的情况下工作:

EXEC dbo.GetProductPivot;
EXEC dbo.GetProductPivot @ProductID = 1;
EXEC dbo.GetProductPivot @ProductID = 2;
EXEC dbo.GetProductPivot @ProductID = 3;
GO

现在让我们添加一个新因素并证明它继续有效:

INSERT dbo.TBLFactors(FactorID, FactorName)
    SELECT 5, 'Tubing';

INSERT dbo.TBLFactorInspect(ID, ProductID, FactorID, FactorValue)
SELECT 6,1,5,2.75;
GO
EXEC dbo.GetProductPivot;
EXEC dbo.GetProductPivot @ProductID = 1;
GO

清理:

DROP TABLE dbo.TBLProduct, dbo.TBLFactors, dbo.TBLFactorInspect;
GO
DROP PROCEDURE dbo.GetProductPivot;
GO