This might not help you but just off the top of my head.... If I were to
write a script to do this I might approach it like this or a rendition of
this. I don't think you can do this sort of thing with DTS but I could be
wrong. I just wrote it out so it would be easier to look at. I hope this
makes sense.
select distinct(foreign_id) from table
cfoutput though query
insert into new_table (id,Foreign_ID) values('IncrementingNumber',
'Whatever current foreign_ID');
update new_table set
<select value from table where foreign_ID='Whatever current
foreign_ID'>
cfoutput though new_query
value#NewIncrementingNumber#='#query.value#'
/cfoutput though query
where foreign_ID='Whatever current foreign_ID'
/cfoutput though query
or you could write a text file for DTS to pick up. The code below would need
to be on one line. You could write the same sort of thing in TQL to be
executed in a stored procedure.
select distinct(foreign_id) from table
Write file
cfoutput though query
"IncrementingNumber", "Whatever current foreign_ID"
<select value from table where foreign_ID='Whatever current
foreign_ID'>
cfoutput though new_query
, "#query.value#"
/cfoutput though query
/cfoutput though query
/Write file
Somebody chime in if they can see a better way.
Thanks,
Dave
-----Original Message-----
From: Timothy Heald [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 29, 2002 9:49 AM
To: CF-Talk
Subject: SQL Question
Hey folks,
I have a table like this:
id foreign id value
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3
Regular old normalized table right? Well I need to get it into columns
like this:
foreignID value1 value2 value3
1 1 2 3
2 1 2 3
Now I know it is possible, but difficult. I would do it with CF but no
matter how high I set the time out in my administrator, and my web server it
times out, or says page cannot be displayed. It is pulling huge amounts of
data, then outputting it it an Excel spread sheet. We tried to do it with a
DTS package and the use SQL Mail to send it, but couldn't get that to work
reliably.
TIA.
Tim Heald
ACP/CCFD :)
Application Development
www.schoollink.net
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
