Normalising a denormalised dataset 1

[Naith]

Hi folks,

I can't get my head straight on the best way to attack this situation.

I have a denormalised table which looks like this:

Country	Product	Week1	Week2	Week3
-------------------------------------
SPAIN	CPU	190	200	280
ITALY	CPU	800	877	788

The problem is I can't plot a dynamic graph on this set up, so I need to create a view whcih normalises this dataset up thusly:

Country	Product	Week	Units
-----------------------------
SPAIN	CPU	1	190
SPAIN	CPU	2	200
SPAIN	CPU	3	280
ITALY	CPU	1	800
ITALY	CPU	2	877
ITALY	CPU	3	788

Any thoughts on the best way to approach this?

Thanks for your time.

 

[bborissov]

SELECT Country,
       Product,
       1 Week,
       Week1 AS Units
FROM MyTable
UNION ALL
SELECT Country,
       Product,
       2     AS Week,
       Week2 AS Units
FROM MyTable
SELECT Country,
       Product,
       3     AS Week,
       Week3 AS Units
FROM MyTable
ORDER BY Country, Product

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Naith]

Union. Of course. Thanks for the kickstart.