Transforming columns to rows 1

[daveonion]

Hi I have an issue where the files I need to import are set out as the folling

TABLE DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7

2 £23 £24 £25 £31 ETC ETC

4 £43 £34 ETC AND SO ON

however i need the data to be changed so it reads as the following,

Table Day Net
2 1 23
2 2 24
2 3 25

etc etc

I am going to be importing the file from Excel, any help or advice would be greatly appreciated

thanks

 

[quatermass]

Hi,

It has been a while since I have done this with DTS, so please forgive any coding errors, but the general formula is as follows


Create a single column table with records of 1-7 (representing the days)

Import the Excel data into a SQL table


As the source for the transform, use both the excel data and the 7 record 'day' table with the following sql statement

select a.*, b.* from 'excel data' a, 'day table' b

Note there is no join.


Create an activex transform and use code as follows:

start code:
dim retnet
dim retday

if source.day number = 1 then
retnet = source.day1 value
retday = 1

if source.day number = 2 then
retnet = source.day2 value
retday = 2

etc...

target.Net = retnet
target.Day = retday

:end code


Without a join , each row in the excel data will be repeated 7 times, one for each day. The code uses the number from the single column table to select the correct field from the excel data to enter into the target table.

This technique can also be used for monthly data. The single field table would have 12 records instead of 7.


Hope this helps

Kevin

**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************