August 1, 2003 at 12:36 pm
Hello,
I have a flat file that I am importing into Sql server and would like to transform the data from a column format to multiple rows. I neet to do this to have the data match our data warehouse structure
Example...
The file has one row for each student. That row has a column for each score on a test.
so the columns look like this:
Student ID, score 1, score 2, score 3
I need to change this to : student ID, ScoreID, score so in the above example this student will now have 3 rows in the data warehouse. I have a solution, but it is pretty bad. I am looking for some fresh ideas on how I might accomplish this with DTS.
Thanks in advance,
Mike
August 1, 2003 at 2:18 pm
I would pump the file into a table that matches the format using Bulk Insert. The following step should be a T-SQL Task which would contain three insert statements like;
insert FinalTable
select StudentID, Score1 from.....
where Score1 is not null
insert FinalTable
select StudentID, Score2 from.....
where Score2 is not null
You get the remainder of the picture.
If you needed to you could make this dynamic to find out the number of scores max and dynamically build the insert statements but that is for another post.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 2, 2003 at 3:58 am
Thanks David,
That is exactly the way I currently have it structured. I read the columns in and do a for loop running the sql each time through the loop. I was just wondering if I had missed the obvious and someone else had different ideas. Again, thanks for your help,
Mike
August 4, 2003 at 5:12 pm
a union query would also do the trick
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply