SQL Query To Set Rows as Columns

Since I am using SQL Server 2000 and don't have the luxury of SQL Server 2005's pivot tables, I need some help on creating a query.

Below find 开发者_如何学运维some sample data.

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

I need help on a query that would output the data to this:

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

I think I would also need another query such that, for example, only the ProductID = 1 was queried, the resulting data would return the factor as columns with which a value has been defined, i.e.:

ProductID   ProductName Length  Threading
1           Jacket      5.00    5.55

Any suggestions highly appreciated. Thanks.

Update: If a stored procedure would be the best answer to achieve this, then I don't have any issues with that as I seem to think that SQL Server 2K's limitations on creating pivots is the main road block here.

I am trying to work on a Dynamic SQL in a Stored Proc solution but I ain't getting far on the dynamic column - value mapping.

I don't have a SQL Server 2000 instance handy to try, but see if this gets you anywhere. I'm a little surprised about the requirement to include columns conditionally - let's hope the application is built to handle the case where columns suddenly appear and disappear depending on the parameters and the changing data in the TBLFactorInspect table.

USE [tempdb];
GO
SET NOCOUNT ON;
GO

Setup:

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

Now some code:

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

Some proof that it works with no parameters or specific productIDs:

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

And now let's add a new factor and prove that it continues to work:

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

Clean up:

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

My first thought would be to use left joins like this: (Everything is explicit, but that works with a finite number of rows in tblFactors)

SELECT
  tblProduct.ProductID,
  tblProduct.ProductName,
  ISNULL(tblLength.FactorValue, '-') AS [Length],
  ISNULL(tblThreading.FactorValue, '-') AS Threading,
  ISNULL(tblWool.FactorValue, '-') AS Wool,
  ISNULL(tblCotton.FactorValue, '-') AS Cotton
FROM
  tblProduct
LEFT JOIN tblFactorInspect tblLength
    ON  tblLength.ProductID = tblProduct.ProductID AND FactorID = 1
LEFT JOIN tblFactorInspect tblThreading
    ON  tblThreading.ProductID = tblProduct.ProductID AND FactorID = 2
LEFT JOIN tblFactorInspect tblWool
    ON  tblWool.ProductID = tblProduct.ProductID AND FactorID = 3
LEFT JOIN tblFactorInspect tblCotton
    ON  tblCotton.ProductID = tblProduct.ProductID AND FactorID = 4

Without dynamic SQL, you need to be explicit about which columns you'll end up with. (In SQL Server 2000 you can only Pivot if you already know what the column names, etc, are going to be. If you need a dynamic number of columns, with dynamic names, SQL Server 2000 can't do this without coding Dynamic SQL)

Additionally, as per a comment, denormalising data is often best done in a GUI, not on the databse itself.

But this should do what you're asking for...

SELECT
  tblProduct.ProductID,
  tblProduct.ProductName,
  MAX(CASE WHEN tblFactorInspect.FactorID = 1 THEN tblFactorInspect.FactorValue END) AS Length,
  MAX(CASE WHEN tblFactorInspect.FactorID = 2 THEN tblFactorInspect.FactorValue END) AS Threading,
  MAX(CASE WHEN tblFactorInspect.FactorID = 3 THEN tblFactorInspect.FactorValue END) AS Wool,
  MAX(CASE WHEN tblFactorInspect.FactorID = 4 THEN tblFactorInspect.FactorValue END) AS Cotton
FROM
  tblProduct
LEFT JOIN
  tblFactorInspect
    ON  tblFactorInspect.ProductID = tblProduct.ProductID
GROUP BY
  tblProduct.ProductID,
  tblProduct.ProductName

Adding WHERE tblProduct.ProductID = 1 will also give what you were asking for as the second half of your question.