Export data from a non-normalized database

I need to export data from a non-normalized database where there are multiple columns to a new normalized database.
One example is the Products table, which has 30 boolean columns (ValidSize1, ValidSize2 ecc…) and every record has a foreign key which points to a Sizes table where there are 30 columns with the size codes (XS, S, M etc…). In order to take the valid sizes for a product I have to scan both tables and take the value SizeCodeX from the Sizes table only if ValidSizeX on the product is true. Something like this:

Products Table
--------------
ProductCode <PK>
Description
SizesTableCode <FK>
ValidSize1
ValidSize2
[...]
ValidSize30

Sizes Table
-----------
SizesTableCode <PK>
SizeCode1
SizeCode2
[...]
SizeCode30

For now I am using a “template” query which I repeat for 30 times:

SELECT
    Products.Code,
    Sizes.SizesTableCode, -- I need this code because different codes can have same size codes
    Sizes.Size_1
FROM Products
INNER JOIN Sizes
ON Sizes.SizesTableCode = Products.SizesTableCode
WHERE Sizes.Size_1 IS NOT NULL
AND Products.ValidSize_1 = 1

I am just putting this query inside a loop and I replace the “_1” with the loop index:

SET @counter = 1;
SET @max     = 30;
SET @sql     = '';

WHILE (@counter <= @max)
BEGIN
    SET @sql = @sql + ('[...]'); -- Here goes my query with dynamic indexes

    IF @counter < @max
        SET @sql = @sql + ' UNION ';
    
    SET @counter = @counter + 1;
END

INSERT INTO DestDb.ProductsSizes EXEC(@sql); -- Insert statement
GO

Is there a better, cleaner or faster method to do this? I am using SQL Server and I can only use SQL/TSQL.

Answer

Well, it seems that a “clean” (and much faster!) solution is the UNPIVOT function.
I found a very good example here:

http://pratchev.blogspot.it/2009/02/unpivoting-multiple-columns.html

Advertisement

Answer