Odd Database format

[hobanwashburne]

I'm working with a database that contains discount codes for product lines by customer. However the format is as follows:

Customer, Product Line 1, Discount 1, Product Line 2, Discount 2,...

where for Customer A Product Line 1 is Milk and Product Line 2 is Eggs,

but for Customer B Product Line 1 is Eggs and Product Line 2 is Milk,

and for Customer C Product Line 1 is Bread and there is no Product Line 2

How can I normalize this data?

[#2]

They is a concept of GENERIC LOAD which is reverse of crosstab. I think you can achieve using that.

[Anonymous]

Scott,

Load first product1, then product2...

table:

load Customer, ProductLine1 as Product, Discount1 as Discount, 1 as ProductNumber from 'your source';

load Customer, ProductLine2 as Product, Discount2 as Discount, 2 as ProductNumber from 'your source';

...add more if more products columns

You will obtain a table with concatenated customers, products and discounts.

Hope it helps.

Marc.

[nagaiank · Accepted solution]

Use a script similar to the following:

FinalTable:

LOAD Customer

          , [Product Line 1] as Product

          , [Discount 1] as Discount

          From YourTable..

          Where Len(Trim([Product Line 1])) > 0;

Concatenate (FinalTable)

LOAD Customer

          , [Product Line 2] as Product

          , [Discount 2] as Discount

          From YourTable..

          Where Len(Trim([Product Line 2])) > 0;

Hope this helps.