Unpivot/Normalize table (1 Viewer)

[David R]

Just wondering if I'm missing an SQL trick for normalizing this data I inherited from an Excel spreadsheet.

Current data:

Case | Insp1 | Insp2 | Insp3 | Insp4 | Insp5 | Rnsp1 | Rnsp2 | Rnsp3 | Rnsp4 | Rnsp5
---------------------------------------------------------------------------------
1234   WEEDS   TRASH   CAR     <null>  <null>  WEEDS    CAR    <null>  <null>  <null>
1235   TRASH   <null>  <null>  <null>  <null>  TRASH   <null>  <null>  <null>  <null>

Normalized table:

Case | Violation | InspectionType
-----------------------------------
1234   WEEDS       Initial 
1234   TRASH       Initial 
1234   CAR         Initial 
1234   WEEDS       Reinspect
1234   CAR         Reinspect 
1235   TRASH       Initial 
1235   TRASH       Reinspect

Right now I'm having to do it with ten different queries (each with its own query behind it to massage the original data), one for each Excel field, and checking for blank entries before appending. And there's going to be about 50 Excel tabs to gather up... anything I'm missing besides the basic slog?

 

[Alansidman]

I think that this link will help you to get your data normalized.

http://www.datawright.com.au/access_resources/normalizing_tables_with_repeating_fields_using_VBA.htm

 

[David R]

Very interesting link, I will give that a try.