getting rid of comma separated column data

working on a transformation, and one of the things i'm trying to get
rid of are columns with comma separated values as lists of data. my god
is that annoying.
anyway, i need an insert and/or update query that will break up the
following comma value problem example (keyword being example, please
don't XXXXX about keys or relationships, it really has nothing to do
with my question):
create table foo (
fooid int identity(1, 1) not null ,
foolistofthingoldids varchar(500)) -- relate to thingoldid
create table things (
thingoldid int identity(1, 1) not null ,
thingnewid uniqueidentifier not null)
-- and this is the new table to replace the comma values in foo
create table foothings (
fooid int not null,
thingnewid uniqueidentifier not null)
it should be noted that i have a user defined split function that can
turn a comma separated list of values into a single column table, with
one row per comma separated item.
the trick is finding a way to use it in a set based operation to fill
foothings with rows, based on the relationships implied in the current
foo table's foolistofthingoldids property. i'm looking for something
like this:
INSERT INTO foothings (fooid, thingnewid)
SELECT f.fooid, t.thingnewid
FROM foo AS f
RIGHT JOIN split(f.foolistofthingoldids, ',') AS l
RIGHT JOIN things AS t ON l.value = t.thingoldid
except, one that actually compiles and works. but this is a sketch of
where i have been leading my train of thought.
thanks in advance for any help!
jasonCourtesy of Kass:
http://www.users.drew.edu/skass/sql...unction.sql.txt
-oj
"jason" <iaesun@.yahoo.com> wrote in message
news:1128945919.133895.197370@.g47g2000cwa.googlegroups.com...
> working on a transformation, and one of the things i'm trying to get
> rid of are columns with comma separated values as lists of data. my god
> is that annoying.
> anyway, i need an insert and/or update query that will break up the
> following comma value problem example (keyword being example, please
> don't XXXXX about keys or relationships, it really has nothing to do
> with my question):
> create table foo (
> fooid int identity(1, 1) not null ,
> foolistofthingoldids varchar(500)) -- relate to thingoldid
> create table things (
> thingoldid int identity(1, 1) not null ,
> thingnewid uniqueidentifier not null)
> -- and this is the new table to replace the comma values in foo
> create table foothings (
> fooid int not null,
> thingnewid uniqueidentifier not null)
> it should be noted that i have a user defined split function that can
> turn a comma separated list of values into a single column table, with
> one row per comma separated item.
> the trick is finding a way to use it in a set based operation to fill
> foothings with rows, based on the relationships implied in the current
> foo table's foolistofthingoldids property. i'm looking for something
> like this:
> INSERT INTO foothings (fooid, thingnewid)
> SELECT f.fooid, t.thingnewid
> FROM foo AS f
> RIGHT JOIN split(f.foolistofthingoldids, ',') AS l
> RIGHT JOIN things AS t ON l.value = t.thingoldid
> except, one that actually compiles and works. but this is a sketch of
> where i have been leading my train of thought.
> thanks in advance for any help!
> jason
>|||Take a look at:
http://www.sommarskog.se/arrays-in-sql.html

> don't XXXXX about keys or relationships, it really has nothing to do
> with my question):
What is the point of writing something like that rather than just
posting the keys and constraints? To pretend that keys have nothing to
do with a problem in SQL is like saying that foundations have nothing
to do with building a house. Keys are fundamental to any data
manipulation problem. I can't figure out much just from a list of
column names and many people probably won't bother to try so I
recommend that if you want a fuller answer you include primary and
foreign keys at least.
David Portas
SQL Server MVP
--|||hmm, maybe i wasn't clear. i already have a user defined function to do
the table creation from comma separated list. i'm just having trouble
with how to use it in the statement i listed.
but the link is useful for the user defined function sers, thanks.|||i find it burdensom and unnecessary, in recreating a simplified version
of a schema to solve something syntactic. at least in some cases. in
this specific case, i'm simply struggling with how to make use of a
user defined function to get data out of an aribitrary comma separated
list, and into a column on another table.
i've read the link you posted several ws ago to come up with the
user defined function that i already mentioned having. perhaps i was
not clear with my question, but i don't need the creation of a table
from a comma separated value. i've had and used that function for a
little while now.
currently i'm trying to figure out how to make use of it in this
specific set-based statement.|||What you're asking for is a recursion. You can do so in sql2k as a cursor or
CTE in Yukon.
-oj
"jason" <iaesun@.yahoo.com> wrote in message
news:1128947946.090802.222680@.f14g2000cwb.googlegroups.com...
> hmm, maybe i wasn't clear. i already have a user defined function to do
> the table creation from comma separated list. i'm just having trouble
> with how to use it in the statement i listed.
> but the link is useful for the user defined function sers, thanks.
>|||i'll look up those topics, thanks. do you have any links or other
keywords that might propel me on my search?|||In the absence of any proper data structure to work with, here's a
solution to a similar problem that may help you:
http://groups.google.co.uk/group/mi...c314d5ea4ef14f5
David Portas
SQL Server MVP
--