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:
into 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. RELATED STORY Presenting Vertical Data Horizontally
|
