|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ID's as an arrayTABLE.LOCATIONS ID NAME 1 VALLEY 2 MOUNTAIN 3 LAKE In another field there are tasks that we do at those locations. If the task applies to multiples, the data is stored once and the LOCATIONSID is an array. TABLE.ACTIVITY ACTIVITY LOCATIONID BIKE ,1,2,3, SKI ,3, TABLE.RESULTS BIKE VALLEY BIKE MOUNTAIN BIKE LAKE SKI LAKE Thanks in advance, Gervaise Gervaise wrote:
Show quote > I have a field in a db, in one table it is the id field. Are you asking a question or making a statement? For the record, this > TABLE.LOCATIONS > ID NAME > 1 VALLEY > 2 MOUNTAIN > 3 LAKE > > In another field there are tasks that we do at those locations. If the task > applies to multiples, the data is stored once and the LOCATIONSID is an array. > TABLE.ACTIVITY > ACTIVITY LOCATIONID > BIKE ,1,2,3, > SKI ,3, > > TABLE.RESULTS > BIKE VALLEY > BIKE MOUNTAIN > BIKE LAKE > SKI LAKE > > Thanks in advance, > > Gervaise > is a bad design. The LocationID column should only contain one value: Activity LocationID -------- ---------- BIKE 1 BIKE 2 BIKE 3 SKI 3 Your query would look like this: SELECT Activity.Activity, Locations.Name FROM Activity INNER JOIN Locations ON Activity.LocationID = Locations.ID Tracy, I am not looking for judgment onthe design. I did not design it. I
am looking for a way to split that array, treat it like a table and join it. thanks though... that query will not work. Show quote "Tracy McKibben" wrote: > Gervaise wrote: > > I have a field in a db, in one table it is the id field. > > TABLE.LOCATIONS > > ID NAME > > 1 VALLEY > > 2 MOUNTAIN > > 3 LAKE > > > > In another field there are tasks that we do at those locations. If the task > > applies to multiples, the data is stored once and the LOCATIONSID is an array. > > TABLE.ACTIVITY > > ACTIVITY LOCATIONID > > BIKE ,1,2,3, > > SKI ,3, > > > > TABLE.RESULTS > > BIKE VALLEY > > BIKE MOUNTAIN > > BIKE LAKE > > SKI LAKE > > > > Thanks in advance, > > > > Gervaise > > > > Are you asking a question or making a statement? For the record, this > is a bad design. The LocationID column should only contain one value: > > Activity LocationID > -------- ---------- > BIKE 1 > BIKE 2 > BIKE 3 > SKI 3 > > Your query would look like this: > > SELECT > Activity.Activity, > Locations.Name > FROM Activity > INNER JOIN Locations > ON Activity.LocationID = Locations.ID > > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > I would create a stored proc and create a temp table to normalize the data,
and return the resultset out of the proc. something like: create #temp ( activity varchar(10), locationid int) Run a query to pull the current values, parse the locationids and insert a row in the temp table for each activity/locationid. Once you have in that format, you can join it up to your Location table and get the data the way you are wanting it. Dan Show quote "Gervaise" wrote: > Tracy, I am not looking for judgment onthe design. I did not design it. I > am looking for a way to split that array, treat it like a table and join it. > thanks though... that query will not work. > > "Tracy McKibben" wrote: > > > Gervaise wrote: > > > I have a field in a db, in one table it is the id field. > > > TABLE.LOCATIONS > > > ID NAME > > > 1 VALLEY > > > 2 MOUNTAIN > > > 3 LAKE > > > > > > In another field there are tasks that we do at those locations. If the task > > > applies to multiples, the data is stored once and the LOCATIONSID is an array. > > > TABLE.ACTIVITY > > > ACTIVITY LOCATIONID > > > BIKE ,1,2,3, > > > SKI ,3, > > > > > > TABLE.RESULTS > > > BIKE VALLEY > > > BIKE MOUNTAIN > > > BIKE LAKE > > > SKI LAKE > > > > > > Thanks in advance, > > > > > > Gervaise > > > > > > > Are you asking a question or making a statement? For the record, this > > is a bad design. The LocationID column should only contain one value: > > > > Activity LocationID > > -------- ---------- > > BIKE 1 > > BIKE 2 > > BIKE 3 > > SKI 3 > > > > Your query would look like this: > > > > SELECT > > Activity.Activity, > > Locations.Name > > FROM Activity > > INNER JOIN Locations > > ON Activity.LocationID = Locations.ID > > > > > > > > -- > > Tracy McKibben > > MCDBA > > http://www.realsqlguy.com > > As Tracy indicated, I don't see the question either.
One could easily surmise that you were seeking confirmation of a design -which, by the way, is sub-optimal. But from your retort to Tracy, I guess that your post has something to do with how to handle a pseudo-array stored in a table field. Here are references to a few different approaches. Hopefully this helps. http://www.sommarskog.se/arrays-in-sql.htmlhttp://www.realsqlguy.com/?p=9 http://www.aspfaq.com/2248 http://realsqlguy.com/twiki/bin/view/RealSQLGuy/ParseDelimitedStringToTable SQL 2005- http://omnibuzz-sql.blogspot.com/2006/06/interesting-queries-using-recursive.html -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Gervaise" <Gerva***@discussions.microsoft.com> wrote in message news:D744ADE3-83D1-44F1-B616-63D1D28D0540@microsoft.com... >I have a field in a db, in one table it is the id field. > TABLE.LOCATIONS > ID NAME > 1 VALLEY > 2 MOUNTAIN > 3 LAKE > > In another field there are tasks that we do at those locations. If the > task > applies to multiples, the data is stored once and the LOCATIONSID is an > array. > TABLE.ACTIVITY > ACTIVITY LOCATIONID > BIKE ,1,2,3, > SKI ,3, > > TABLE.RESULTS > BIKE VALLEY > BIKE MOUNTAIN > BIKE LAKE > SKI LAKE > > Thanks in advance, > > Gervaise > Gervaise (Gerva***@discussions.microsoft.com) writes:
Show quote > I have a field in a db, in one table it is the id field. SQL 2000 or SQL 2005?> TABLE.LOCATIONS > ID NAME > 1 VALLEY > 2 MOUNTAIN > 3 LAKE > > In another field there are tasks that we do at those locations. If the > task applies to multiples, the data is stored once and the LOCATIONSID > is an array. > > TABLE.ACTIVITY > ACTIVITY LOCATIONID > BIKE ,1,2,3, > SKI ,3, -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 2000
Show quote "Erland Sommarskog" wrote: > Gervaise (Gerva***@discussions.microsoft.com) writes: > > I have a field in a db, in one table it is the id field. > > TABLE.LOCATIONS > > ID NAME > > 1 VALLEY > > 2 MOUNTAIN > > 3 LAKE > > > > In another field there are tasks that we do at those locations. If the > > task applies to multiples, the data is stored once and the LOCATIONSID > > is an array. > > > > TABLE.ACTIVITY > > ACTIVITY LOCATIONID > > BIKE ,1,2,3, > > SKI ,3, > > SQL 2000 or SQL 2005? > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Hello,
The RAC utility can very easily do exactly what you want. Go to the online help: http://www.rac4sql.net/onlinehelp.asp Expand to: Working with Character Strings Splitting Strings Splitting Delimited Strings into Rows Splitting by Position Visit RAC @: www.rac4sql.net Gervaise (Gerva***@discussions.microsoft.com) writes:
Show quote > "Erland Sommarskog" wrote: To bad then. You will have to loop over the table, using any of the > >> Gervaise (Gerva***@discussions.microsoft.com) writes: >> > I have a field in a db, in one table it is the id field. >> > TABLE.LOCATIONS >> > ID NAME >> > 1 VALLEY >> > 2 MOUNTAIN >> > 3 LAKE >> > >> > In another field there are tasks that we do at those locations. If the >> > task applies to multiples, the data is stored once and the LOCATIONSID >> > is an array. >> > >> > TABLE.ACTIVITY >> > ACTIVITY LOCATIONID >> > BIKE ,1,2,3, >> > SKI ,3, >> >> SQL 2000 or SQL 2005? >> > > 2000 methods I discuss on http://www.sommarskog.se/arrays-in-sql.html. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics