Table / Data definition problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table / Data definition problem

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-09-08 : 11:58:48

Hi,

I have a sales_order_history table and a stock_table. The fields in the SOH table are:

Order_No
Product_01
Quantity_01
Price_01
Product_02
Quantity_02
Price_02
Product_03
Quantity_03
Price_03
Product_04
Quantity_04
Price_04

The product field stores a product_code which should relate to the stock table. The stock table contains a product_code and product_description.
This data is provided to me by a client who extracts this from their till system. The problem is that I want to rearrange the data so that there are multiple records for the same order_no, but only a single product_code, quantity and price per record. At the moment there are four seperate product codes, prices and quantities recorded per record.
I want to do this so that I can easily identify for example all peopole who have bought product 234 and what was the total spent. With the data stored as it is I would have to run a query on all of the four different product code fields in order to find the customers and what they spent.

Does this mean that this is not Normalised?

Any help would be greatly appreciated.

Cheers

Paul

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-08 : 12:19:52

quote:
Does this mean that this is not Normalised?

Yes

SELECT	Order_No
	, Product_01 'ProductNum'
	, Quantity_01 'Quantity'
	, Price_01 'ProductPrice'

FROM	sales_order_history

union

SELECT	Order_No
	, Product_02
	, Quantity_02
	, Price_02

FROM	sales_order_history
WHERE   Product_02 IS NOT NULL

union

SELECT	Order_No
	, Product_03
	, Quantity_03
	, Price_03

FROM	sales_order_history
WHERE   Product_03 IS NOT NULL

union

SELECT	Order_No
	, Product_04
	, Quantity_04
	, Price_04

FROM	sales_order_history
WHERE   Product_04 IS NOT NULL

You could (and probably should) move the price field to the stock_table to be even more normalised.

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-09-09 : 05:51:25

Excellent, thanks Drymchaser!!

This means that I can add in the remaining fields from the order history table and then recreate the table so that it is normalised.

Cheers

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources