SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL parsing question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

becksinthecity
Starting Member

18 Posts

Posted - 08/04/2005 :  15:50:20  Show Profile  Reply with Quote
I know this should be simple, but for some reason i am having difficulties! I have a table that has 2 columns i am interested in, Computer and Userlist. The computer field has one name, but the userlist column can vary from 1 user to 8 or more, seperated by semicolons. eg (joe;sally;lou;)
i need to link this table with another table that has a list of computer names and users, but the users in this second table are singular, ie one user per column. is there an easy way to do a WHERE statement to match up the singular user to the string of users?

ex: WHERE table2.user in (table1.userlist) and table2.computer=table1.computer


or would it be easier to parse out the first list of users into a new table that has each username matched up with a computer

current:
computer=NYcomputer1, userlist=joe;sally;lou;

wanted:
computer=NYcomputer1, user=joe
computer=NYcomputer1, user=sally
computer=NYcomputer1, user=lou



any help is appreciated! thanks!




X002548
Not Just a Number

15586 Posts

Posted - 08/04/2005 :  16:21:49  Show Profile  Reply with Quote
There are lots of articles on SQL team, and there are always the threads to search for answers, but I think you want to read this

http://www.sqlteam.com/item.asp?ItemID=11499


EDIT: Who denormalized the data in the first place. Very bad form that.


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - X002548 on 08/04/2005 16:22:37
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 08/04/2005 :  16:26:09  Show Profile  Reply with Quote
we're pulling data from the computers directly via a 3rd party application and dumping it into SQL on a regular basis. Along with a lot of other data, it returns anything with more than one value in a semicolon seperated state.... which is quite possibly the most annoying thing i've run into in a long time, but due to the mass amounts of data we are pulling, this is the most efficient way to pull the data... we just have to get creative in pulling out the information we want.
Go to Top of Page

becksinthecity
Starting Member

18 Posts

Posted - 08/04/2005 :  16:47:26  Show Profile  Reply with Quote
ok i'm confused, here is the function i made:


ALTER Function SemiToChar ( @Array varchar(1000))
returns @CharTable table
(CharValue varchar)
AS
begin

declare @separator char(1)
set @separator = ';'

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ';'

while patindex('%;%' , @array) <> 0
begin

select @separator_position = patindex('%;%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @CharTable
Values (Cast(@array_value as varchar))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end



and now for my select query i'm using:

Declare @TheCSV varchar(100)
set @TheCSV = localadministrators

select computername, LocalAdministrators
from dbo.bigfix_host_data
JOIN requinn.SemiToChar(@TheCSV) CSV
ON CSV.CharValue = localadministrators



but it is telling me i can't use my column name as the variable value.... what am i doing wrong?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/05/2005 :  00:41:22  Show Profile  Reply with Quote
It needs

set @TheCSV = 'joe;sally;lou;'

so to process this on every row in the bigfix_host_data table you will have to loop round one-by-one - best to insert the split data into a "wanted" table as you described earlier.

Going forwards you could do that in a trigger on bigfix_host_data whenever a row is inserted, or the LocalAdministrators column is changed, so that it freshens up your proposed "wanted" column

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000