Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a data table that contains records like this:

ZIP       Ground  Air
010-041   005     305
150-163   004     304

As I have it right now, it's actually an excel file but I eventually need to convert it into a table in Oracle. The problem is that the format of the first column is a range but I need it as discrete values i.e.

ZIP

10    005     305
11    005     305
12    005     305
13    005     305
14....

What's the easiest way to accomplish this in excel and/or oracle?

share|improve this question

2 Answers 2

Assuming you have inserted the data into Oracle perhaps using an external table, you could use the connect by clause of a hierarchical query to generate the additional rows you need.

Setup to simulate the source data:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (Zip Varchar2(7), Ground Varchar2(3), Air Varchar2(3));
INSERT INTO t1 VALUES ('010-041','005','305');
INSERT INTO t1 VALUES ('150-163','004','304');

Solution:

CREATE TABLE t2 AS (
  SELECT substr(zip,1,3) + Level - 1 Zip, Ground, Air 
  FROM t1 a CONNECT BY level <= substr(zip,5,3)-substr(zip,1,3)
  );

If you'd rather do more work on the Excel side, then you could create the following table in Oracle:

CREATE TABLE t1 (Zip Number(3), Ground Varchar2(3), Air Varchar2(3));

Then run a formula like this for each row in Excel:

=CONCATENATE("INSERT INTO t1 (SELECT ",MID(A2,1,3),
    "+Level-1,'",B2,"','",C2,"' FROM dual connect by level <=",MID(A2,5,3)-MID(A2,1,3),");")

This will produce insert statements that you can then run in Oracle. They will look like this:

INSERT INTO t1 (SELECT 010+Level-1,'005','305' FROM dual connect by level <=31);
share|improve this answer

May I also suggest that, depending on how you will use the table, you don't necessarily need each potential value in its own row. You can join on ranges:

SELECT T.Something, Z.Ground, Z.Air
FROM
   Table T
   INNER JOIN ZipRanges Z
      ON T.Zip BETWEEN Z.StartZip AND Z.EndZip

This should be highly performant with the proper indexes, and saves you potentially a bunch of hassle dealing with all the different values. I'd personally much rather keep the data in its compact form if at all possible.

share|improve this answer
    
Good point, which may be useful depending on the requirements. –  Leigh Riffel Mar 16 '12 at 21:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.