Hello,
I have no trouble converting a comma-delimited string of values into multiple records, bur have recently encountered a problem that's giving me a headache - hopefully someone on the forum already has some experience doing this task:
create table tester (col1 int, col2 varchar(1000), col2 varchar(1000))
insert into tester values(1, '1,3,5,7', 'a,c,e,g,')
insert into tester values(2, '11,13,15,17', 'aa,ac,ae,ag,')
There is no correlation between rows, but between the 2 varchar columns is a positional relationship - in the first record, the '1' in col2 relates to the 'a' in col 3, same for the '3' and 'c', on and on. The values within each of the comma-delimited strings of the 2 columns are positionally related. Say they could be time and temperature values, with a string of time values in col1 and a string of related temps in col2. This is data from an external system that I have no control over, but must load the data into my system
I need to write a select statement that will return the contents like so:
1, 1, a
1, 3, c
1, 5, e
1, 7, g
2, 11, aa
2, 13, ac
2, 15, ae
2, 17, ag
Has anyone encountered such as this? Any clues or code snippets?
Thanks for any ideas,
saWhat split function do you use? If it uses a tally table you could return the numbers and link on those.
Not used but how about Kristen's here?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2|||your "relationships" are based on offsets...so what value or meaning does that infer??
what the hell...use a cursor
actually a simple loop and udf will do
