Query Help

[adamgray1209]

How would I get the following table:


FRUIT SHOP1 SHOP2 SHOP3
RedApple 66
GrnApple 74 444 5
Pear 21 12
Peach 321
Orange 321 1

Into this.....

FRUIT SHOP QTY
RedApple Shop3 66
GrnApple Shop1 74
GrnApple Shop2 444
GrnApple Shop3 5
...

Thanks,

Adam.

[BruceG]

You could do something like this:

SELECT FRUIT, 'Shop1' AS SHOP, SHOP1 AS QTY
    FROM FruitTable
    WHERE SHOP1 IS NOT NULL
UNION 
SELECT FRUIT, 'Shop2', SHOP2
    FROM FruitTable
    WHERE SHOP2 IS NOT NULL
UNION 
SELECT FRUIT, 'Shop3', SHOP3
    FROM FruitTable
    WHERE SHOP3 IS NOT NULL
ORDER BY 1, 2

If you wanted to put this data into a new table, you would make the new table first, then you could run this INSERT statement:

INSERT INTO NewTable (FRUIT, SHOP, QTY)
SELECT FRUIT, 'Shop1' AS SHOP, SHOP1 AS QTY
    FROM FruitTable
    WHERE SHOP1 IS NOT NULL
UNION 
SELECT FRUIT, 'Shop2', SHOP2
    FROM FruitTable
    WHERE SHOP2 IS NOT NULL
UNION 
SELECT FRUIT, 'Shop3', SHOP3
    FROM FruitTable
    WHERE SHOP3 IS NOT NULL

Note: If your existing "SHOP1/SHOP2/SHOP3" fields have zero instead of NULL, change the WHERE clauses in my example to:
WHERE SHOPx > 0