First Normal Form On The Fly
July 10, 2013 Ted Holt
Converting normalized data into a repeating group is a common requirement, and we have discussed that topic several times in Four Hundred Guru, even in this issue. Sometimes the need arises to convert data in the other direction, i.e., to convert a repeating group to a normalized form. This is a handy technique to know, and it’s easy.
Suppose you’ve been given a spreadsheet with 13 columns of data. The first column is an item number. The next 12 columns are replacement costs for each month. You’ve been asked to load the data into a normalized table (physical file) of three columns (fields):
That is, you need to convert this:
To convert columns to rows, use UNION ALL.
Assuming you’ve copied the spreadsheet data into a table called ITEMCOSTS and will load table COSTHIST, here’s the SQL you need.
insert into costhist select c.ItemNumber, '2012-01-01', c.cost01 from itemcosts as c union all select c.ItemNumber, '2012-02-01', c.cost02 from itemcosts as c union all ( . . . etc. for months 3 - 11 . . .) union all select c.ItemNumber, '2012-12-01', c.cost12 from itemcosts as c order by 1,2
The expression requires a SELECT for each “bucket”. In this case, there are 12 select statements because there are 12 months.
UNION would produce the same result as UNION ALL, but UNION ALL performs much better because it does not check for duplicate rows.
I would hate to use this technique with the file that FHG reader David told me about recently. It has six repeating fields, each one of which has 90 buckets. (Yes, 90!) But I suppose it could be done.