Un cross-tabulate import DTS thing 1

[cacbomb]

Hello,
I am using a DTS package to import 4 files (identical structures, different data) into one SQL table. Then I need to "un cross-tab" the table. What I mean is that there is a field for each of 27 pay periods and the table covers 3 years, which means there are 81 fields containing numbers. I need to transfer this data into an other table which has a period field and a number field in which I will place the date of the period and then the corresponding value.

Example:

Original table

Project Period1 Period2 Period3 Period4 ...
0001 11 12 13 14
0002 21 22 23 24


Desired transformation:

Project Period Number
0001 1 11
0001 2 12
0001 3 13
...
0002 1 21
0002 2 22
...

I am attempting to do the transformation though vb script which will run 81 insert queries. I am wondering if anyone has any better ideas.

Thank you,
William

 

[redlam]

Hi.
I'm assuming that you only have 4 periods to deal with here. If this is the case, I think the easiest, least complicated solution would be the following:

/*insert data from 'crossed' table into new 'uncrossed'
table*/
insert into
project_uncrossed
(project,
period,
number)
select project,
1,
period1
from project_crossed

insert into
project_uncrossed
(project,
period,
number)
select project,
2,
period2
from project_crossed

insert into
project_uncrossed
(project,
period,
number)
select project,
3,
period3
from project_crossed

insert into
project_uncrossed
(project,
period,
number)
select project,
4,
period4
from project_crossed

/*view your uncrossed results*/
select * from project_uncrossed order by project, period