How to re-shape data

[xlbo]

Hello all,

I have the following table layout:

[tt]
BusinessUnit | TimeKey | ExpectedBeer | ActualBeer |Expected Cider | ActualCider etc etc for 8 varieties of drinks
[/tt]

There are 3 timekeys that I am interested in for each BusinessUnit (report will run for 1 BusinessUnit ID at a time so not a huge amount of data)

I need to get this in the shape of:
[tt]
ProductType | Expected_TK1 | Actual_TK1 | Expected_TK2 | Actual_TK2 | Expected_TK3 | Actual_TK3
Beer 10 8 12 12 17 9
Cider 7 7 4 6 10 4
etc etc
[/tt]

Can use a stored proc if necessary but currently fiddling about with lots and lots of little queries unioned together. I'm kinda self taught at SQL so don't know if this is the only/best way to do it - seems to take a lot longer than I would expect however

Queries are along the lines of

Select 'Beer' as ProductType, ExpectedBeer As Expected, ActualBeer as Actual
FROM Table WHERE TimeKey = 'TK1' AND BusinessUnit = 'BU1'
Union
Select 'Cider' as ProductType, ExpectedCider As Expected, ActualCider as Actual
FROM Table T WHERE TimeKey = 'TK1' AND BusinessUnit = 'BU1'
Union ....

This query is repeated for the other 2 time keys and the results joined together then the final recordset is selected from the 3 joined temp tables

If anyone can point me in the right direction to speed this query up I would most appreciate it

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SQLSister]

What you need is a redesign of the table.
I would suggest a structure of
BusinessUnit, Timekey, ProductType, Expected, Actual

This is expanable as the products change in time and much easier to query.

If you cannot change the design, you should be able to use Union all instead of union to speed things up a bit.

"NOTHING is more important in a database than integrity." ESquared

 

[xlbo]

Heh - yeh - much as I would love to re-design the table I don't have the authority to do it - there are other things that work off the back of this (badly designed) table so unfortunately (for now) I am stuck with it in the current layout. Can't even use the excuse of products changing cos they won't really - they are product categories that havn't changed since the introduction of alcopops about 12 years ago!

Thanks for the union all tip - will go and google that now!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question