0

I need to clean up some ugly data. What I have is similar to

ID,someFields,Supplier,Supplier_1,Supplier_2,Price,Price_1,Price_2,Weight; Weight_1,Weight_2

and so forth. Fields are named up to _9 and there are actually 8 different such fields named _1 to _9. Of course Price_1 is for Supplier_1 and so forth.

I would now like to unpivot to 

ID,someFields,Supplier,Price,Weight

by duplicating ID and somefields. An important note is that those _1 to _9 fields can be null, in fact most of them are.

Tools I have. Excel MS Access could (mis)use oracle schema I have access to...

I found this How to simulate UNPIVOT in Access 2010?

However that also multiplies rows that only have 1 Supplier.

Any ideas?

Community
  • 1
  • 1
beginner_
  • 7,230
  • 18
  • 70
  • 127

1 Answers1

1

You can use a union query.

 SELECT * INTO NewTable FROM
  (SELECT ID,someFields,Supplier,Price,Weight FROM Table
   WHERE SomeField Is Not Null
   UNION ALL
   SELECT ID,someFields1,Supplier1,Price1,Weight1 FROM Table
   WHERE SomeField1 Is Not Null
   <...>)
Fionnuala
  • 90,370
  • 7
  • 114
  • 152