| Author |
 Topic  |
|
|
becksinthecity
Starting Member
18 Posts |
 Posted - 08/04/2005 : 15:50:20
|
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 |
|
|
becksinthecity
Starting Member
18 Posts |
Posted - 08/04/2005 : 16:26:09
|
| 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. |
 |
|
|
becksinthecity
Starting Member
18 Posts |
Posted - 08/04/2005 : 16:47:26
|
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? |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 08/05/2005 : 00:41:22
|
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 |
|
|
| |
Topic |
|
SQL parsing question
