SQL 2005 Comma Separated List - SQL Server Forums

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)

SQL 2005 Comma Separated List

Author

Topic

jamie1984
Starting Member

6 Posts

Posted - 2008-06-26 : 10:58:57

Hi, looking for some advice.

I have two tables:

1. tblUsers
2. tblCustomers

within the users table i would like to store a reference to the customers id in a comma separated list. This will control a users access to customer.

for example user A has values 1,2,3,4 stored in a field called "CustomerIDRef" in tblUsers. I then want to query this, e.g:

select all customers from tblCustomers where CustomerIDRef is in the comma separeted list.

hope this makes sense to someone

any advice on whether I'm going about this the wrong way or how i can achieve this?

ive put together this screenshot to help explain
[url]http://filemanager.rcnit.co.uk/Clients/RCN/JimsStuff/image.jpg[/url]

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 11:03:59

SELECT u.User,c.Customer FROM tblCustomers c
INNER JOIN tblUsers u
ON ','+u.CustomerIDRef+',' LIKE '%,'+CAST(c.CustomerIDRef AS varchar(10)) + ',%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 11:04:00

1 Read about Normalization
2 Where ','+Csvcolumn+',' like '%,'+cast(othercolumn as varchar(10))+,%'

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-26 : 11:04:30

[Sniped]
by a second

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 11:04:47

quote:
Originally posted by madhivanan

1 Read about Normalization
2 Where ','+Csvcolumn+',' like '%,'+cast(othercolumn as varchar(10))+,%'

Madhivanan

Failing to plan is Planning to fail

Beat by 1 second!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources