Point in the right direction.. lists in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Point in the right direction.. lists in a column

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-23 : 16:53:33

Hi all,
I have a project were I am using a DB that was originally created by people that had no right create tables etc. Long story short I am stuck with creating some SPs for them.

What I have are columns that have lists in them (comma separated values). These values need to be joined (coded lookup style if you will), to another table. There are 4 columns that would need with each column containing what appears to be nor more then three in each list.

Example


States      City           Members
------      ------         -------
1,2,3       101,103        999,990,999

And of course the numbers in the list correspond to a column in another table.

I have a function to break out comma separated lists and am thinking that somehow I need to rebuild a temp table and then join that to the lookup tables.

Not looking for someone to do the coding, just a general idea if I am on the right path, or is there an easier one?

Thanks,
Chris

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 17:00:24

If your function can return a table that will be good. You can just join that with your lookup table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-23 : 17:23:10

Dinakar,

Actually what I have this as a row in a temp table. The function just returns a a table (I think). Here is the function:


Create  FUNCTION dbo.Split
(
	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
	Return
END

I am assuming that I can come close to something like this ?


SELECT *
FROM  dbo.Job INNER JOIN
          dbo.States ON States.States = [DATA] FROM DBO.SPLIT(@CommaString, ',')

I know this is way off base but gives and idea as to what I am trying to do.

Thanks again,
Chris

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 17:47:51

[code]

Declare @s table (stid int, stname varchar(50))
insert into @s values (1, 'california')

Declare @CommaString varchar(10)
Set @CommaString = '1,2,3'

select *
from DBO.SPLIT(@CommaString, ',') a
join @s S on a.data = s.stid

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-23 : 18:33:39

Dinakar,

Thanks for the help that gets me going. Although I messed up in my example above. In the above example I used I used a variable (copied it from a previous SP).

How would I go about splitting a column that was contained in the table being joined?

Thanks again for the help,
Chris

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 18:37:41

Does your table have multiple rows or just one row?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-24 : 08:05:39

Dinakar,

The table has multiple rows.

Chris

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 12:10:49

I am afraid you might have to loop through the table...get the row value into a variable and use it as above..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-24 : 13:03:05

Dinakar,
I knew that was coming. :(

I wonder if it might be better to break all the columns that contain the comma deliminated strings and recreate a column for each one in the temp table and then join the tables that way?

Thanks again for the ideas,
Chris

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 13:22:49

Yes but that would involve looping too right?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 13:28:00

Perhaps if you can write a PIVOT query and transform the rows to columns, then concatenate all the columns into one column, you will end up with a big concatenated list of all the SattesId's. But if you have too many rows it might be a problem..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

chrispy
Posting Yak Master

107 Posts

Posted - 2007-08-24 : 14:02:50

quote:
Originally posted by dinakar

Yes but that would involve looping too right?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

True but I just found out that at the most (ever) it would be no more then 1000 rows. I might just try it and see how she performs. I think with only 1000 rows the performance hit might not be that bad.

Thanks for all the great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources