这是在脚本中标准化表的示例。我建议你做这样的事情
e.g Table: tbl_tmpDataDate, ProductName, ProductCode, ProductType, MarketDescription, Units, Value2010-01-01, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 1', 20, 20.002010-01-02, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 2', 40, 40.002010-01-03, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 3', 40, 40.002010-01-01, 'Cola', '02', 'Drink', 'Store 1', 40, 80.002010-01-02, 'Cola', '02', 'Drink', 'Store 2', 20, 40.002010-01-03, 'Cola', '02', 'Drink', 'Store 2', 60, 120.002010-01-01, 'Simiri Gum', '03', 'Gum', 'Store 1', 40, 80.002010-01-02, 'Simiri Gum', '03', 'Gum', 'Store 2', 20, 40.002010-01-03, 'Simiri Gum', '03', 'Gum', 'Store 3', 60, 120.00
您将首先创建日期表：
CREATE TABLE tbl_Date(DateID int PRIMARY KEY IDENTITY(1,1) ,Datevalue datetime)INSERT INTO tbl_Date (Datevalue)SELECt DISTINCT DateFROM tbl_DataWHERe Date NOT IN (SELECt DISTINCT Datevalue FROM tbl_Date)
然后，您将创建您的市场表
CREATE TABLE tbl_Market(MarketID int PRIMARY KEY IDENTITY(1,1) ,MarketName varchar(200))INSERT INTO tbl_Market (MarketName)SELECt DISTINCT MarketDescriptionFROM tbl_tmpDataWHERe MarketName NOT IN (SELECt DISTINCT MarketDescription FROM tbl_Market)
然后，您将创建您的ProductType表
CREATE TABLE tbl_ProductType(ProductTypeID int PRIMARY KEY IDENTITY(1,1) ,ProductType varchar(200))INSERT INTO tbl_ProductType (ProductType)SELECt DISTINCT ProductTypeFROM tbl_tmpDataWHERe ProductType NOT IN (SELECt DISTINCT ProductType FROM tbl_ProductType)
然后，您将创建您的产品表
CREATE TABLE tbl_Product(ProductID int PRIMARY KEY IDENTITY(1,1), ProductCode varchar(100), ProductDescription varchar(300) ,ProductType int)INSERT INTO tbl_Product (ProductCode, ProductDescription, ProductType)SELECt DISTINCT tmp.ProductCode,tmp.ProductName, pt.ProductTypeFROM tbl_tmpData tmpINNER JOIN tbl_ProductType pt ON tmp.ProductType = pt.ProductTypeWHERe ProductCode NOT IN (SELECt DISTINCT ProductCode FROM tbl_Product)
然后，您将创建数据表
CREATE TABLE tbl_Data(DataID int PRIMARY KEY IDENTITY(1,1), DateID varchar(100), ProductID varchar(100), MarketID varchar(300) ,Units decimal(10,5) , value decimal(10,5))INSERT INTO tbl_Data (ProductID, MarketID, Units, Value)SELECt t.DateID , p.ProductID , m.MarketID , SUM(tmp.Units) , SUM(tmp.VALUE)FROM tbl_tmpData tmpINNER JOIN tbl_Date t ON tmp.Date = t.DatevalueINNER JOIN tbl_Product p ON tmp.ProductCode = p.ProductCodeINNER JOIN tbl_Market m ON tmp.MarketDescription = m.MarketName GROUP BY t.DateID, p.ProductID, m.MarketIDORDER BY t.DateID, p.ProductID, m.MarketID
