SQL Query To Set Rows as Columns

Question

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.

Answer 1

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

Answer 2

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