Table restructure Problem

From: Qingbo Zheng <qingbo_at_udel.edu>
Date: Thu, 01 Jul 1999 13:54:00 +0100
Message-ID: <377B64E8.33F0073D_at_udel.edu>

Hi everyone:
I'm working on a survey data project that requires to convert spreadsheet data to Oracle tables and I found this problem very challenging and I was wondering if anyone can have some ideas share with me.

The original spreadsheet file has the following format

Table 1:

                        Geographic Area: BRANDYWINE

GRADE Y1990 Y1991 Y1992 Y1993 Y1994 Y1995 Y1996 Y1997 Y1998

PK	18	24	2	47	39	49	32	48	41
K	856	856	875	805	750	733	773	727	707
G-1	1057	973	919	975	908	931	906	935	930
G-2	983	980	935	891	978	865	920	897	912
G-3	930	930	972	928	869	973	866	912	891
G-4	963	888	882	890	908	859	922	822	880
G-5	901	937	890	864	887	891	871	921	865
G-6	883	908	934	891	898	924	923	868	933
G-7	855	981	940	967	922	933	964	984	993
G-8	849	840	937	915	965	866	918	937	895
G-9	853	944	991	991	963	1073	972	1021	1017
G-10	760	816	817	849	818	833	893	806	869
G-11	668	615	668	696	741	701	694	799	714
G-12	704	710	675	665	689	723	720	696	797
TOTAL	11280	11402	11437	11374	11335	11354	11374	11373	11444


Table 2:
			Geographic Area: APPOQUINIMINK 	
				
GRADE	Y1990	Y1991	Y1992	Y1993	Y1994	Y1995	Y1996	Y1997	Y1998

PK	6	7	4	5	4	7	3	5	11
K	250	257	232	269	273	274	286	341	366
G-1	210	236	274	261	314	320	319	334	399
G-2	212	231	259	300	263	326	335	355	368
G-3	218	215	262	266	330	281	358	359	392
G-4	226	224	240	284	280	357	317	382	396
G-5	221	234	230	258	311	290	345	334	405
G-6	202	230	251	237	283	345	323	364	368
G-7	206	199	247	262	245	300	327	344	407
G-8	207	227	215	248	271	260	312	346	352
G-9	164	183	211	196	240	296	297	323	301
G-10	132	150	172	197	190	213	281	260	340
G-11	109	119	128	149	175	151	165	224	221
G-12	107	111	119	140	169	170	160	181	200
TOTAL	2470	2623	2844	3072	3348	3590	3828	4152	4526

...

I have 29 tables like that

I need to convert them to the following tables

                                Year: 1990

Geo_Area PK K G-1 G-2 G-3 G-4 G-5 G-6 G-7 G-8 G-9 G-10 G-11 G-12

APPOQUINIMINK
BRANDYWINE
...
...
...
...
...

                                    Year: 1991

Geo_Area PK K G-1 G-2 G-3 G-4 G-5 G-6 G-7 G-8 G-9 G-10 G-11 G-12

APPOQUINIMINK
BRANDYWINE
...
...
...
...
...

                                Year: 1992

Geo_Area PK K G-1 G-2 G-3 G-4 G-5 G-6 G-7 G-8 G-9 G-10 G-11 G-12

APPOQUINIMINK
BRANDYWINE
...
...
...
...
...

My idea to do this is to use PL/SQL dynamic cursors to fetch columns from original tables and insert into new tables but I doubt that is an efficient way. So if you have better ideas, please help me!

thanks

qingbo

Center for Applied Demography and Survey Research University of Delaware Received on Thu Jul 01 1999 - 14:54:00 CEST