March 12, 2002 at 9:12 am
Any advice on this matter would be greatly appreciated. I have been using DTS for straightforward stuff so far, but now have the following difficulty.
I have data in 170 tables, each of which contains exactly 20 rows of 6 columns, i.e 120 cells.
I want to transform each table or set of 120 into one (single)column for eventual exporting into a new table. The data needs to be read row by row and moved into the single column in that order.
Please forgive my ignorance if the solution to this is a simple one!
March 13, 2002 at 5:33 am
quote:
I want to transform each table or set of 120 into one (single)column for eventual exporting into a new table. The data needs to be read row by row and moved into the single column in that order.
Not exactly sure what you mean do you mean if column 1 looked like this
1
4
8
12
2
3
The you want the output to be 30 to the column 1 of the final table.
Can you give me a visual of what you have and what you want it to do?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 13, 2002 at 6:08 am
The current table looks like:
100 350 200 45 0 600
125 225 340 125 55 0
800 120 560 450 300 900
....
(total of 20 rows like this)
I am trying to transform this by reading along each row and putting each individual cell into a table that has one column, so it looks like:
100
350
200
45
0
600
125
225
340
125
55
0
800
120
560
450
300
900
.....
Hope this helps explain things, and thanks for your time.
March 13, 2002 at 8:18 am
You would need to do something like
SELECT col1 AS DataX FROM tblX
UNION ALL
SELECT col2 FROM tblX
UNION ALL
SELECT col3 FROM tblX
UNION ALL
SELECT col4 FROM tblX
But the problem is the order will not come out as like across your table as you will stack col1 rec1-reclast then col2 rec1-reclast and so on.
If you have a column that the data is sorted on you could do te following:
SELECT DataX FROM
(
SELECT col1 AS DataX, colob AS OrdCol FROM tblX
UNION ALL
SELECT col2, colob FROM tblX
UNION ALL
SELECT col3, colob FROM tblX
UNION ALL
SELECT col4, colob FROM tblX
ORDER BY
OrdCol
) As PreSortedTbl
If this does not help let me know as your option then will be Dynamic SQL (may take a bit but can be done) or a cursor (last resort).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 13, 2002 at 10:02 am
Another solution is to use a DataPump DTS task with an ActiveX transform script. The source is your source table. Define a lookup as the Insert into the single column table. In the script define a counter loop that can bump through the DTSDatasource one column at a time ordinally (1-6 for each column) Call the lookup insert for each column of each row and you're done. Don't forget to use SkipInsert as the return code so that no rows are written to your destination "place holding" table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply