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 2000 Forums

SQL Server Development (2000)

Comma Separated List

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-05-07 : 21:36:17

Hi,

I've been asked to do a tricky query and I'm not sure how to do it. I have a list of accounts with product codes. I then have a list of the codes with a description. It's not just a simple join statement though between code IDs. Here is what I mean.

TABLE A
Account NUM - Feature Codes
8082459328 - DAVI,DBVI,DCPK
8082490978 - CWT,ATWC
8082540494 - DAVI,CWT,SC8V,STWC,TWC
8082542831 - DAVI,CLND,CNDE,CWT,STWC,TWC

TABLE A shows a list of accounts with their features. Note that they have more than one feature in a comma separated list.

TABLE B
DAVI - Call Forwarding - Single Line
DBVI - Call forwarding busy line variable
DCPK - Directed Call Park
DCPU - Directed Call Pickup
DENY - Deny Access to CLASS Features

TABLE B has descriptions for each of these codes.

EXPECTED OUTPUT:

For the first entry ... 8082459328 - DAVI,DBVI,DCPK

I want it to output in the description field like this...

Call Forwarding - Single Line Call forwarding busy line variable Deleted Call Park

It doesn't have to look real pretty. My problem is when I try join statements and LIKE functions, it only picks up where there is a 1 to 1 relationship. I can't get it to bring in multiple descriptions when there are multiple codes.

Any help would be appreciated.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 02:18:44

Those Feature Codes should be in a separate table, joined to TABLE A, not in a comma-separated-column!

You can SPLIT the Feature Codes in that column - preferably into a new permanent table, but failing that into a temporary table - and then JOIN then back. And then you can make a concatenated description.

Kristen

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-08 : 02:25:44

Declare @tt table (id bigint, com varchar(50))
insert @tt
select 8082459328,'DAVI,DBVI,DCPK' union all
select 8082490978, 'CWT,ATWC' union all
select 8082540494, 'DAVI,CWT,SC8V,STWC,TWC' union all
select 8082542831, 'DAVI,CLND,CNDE,CWT,STWC,TWC'

declare @yy table (val varchar(5), des varchar(50))
insert @yy
select 'DAVI','Call Forwarding - Single Line' union all
select 'DBVI','Call forwarding busy line variable' union all
select 'DCPK','Directed Call Park' union all
select 'DCPU','Directed Call Pickup' union all
select 'DENY','Deny Access to CLASS Features'

Select id, Description = (col1 + ' ' + col2 + ' ' + col3 ) From
(Select id,
Max(Case when val = 'DAVI' then des else '' end) as col1,
max(Case when val = 'DBVI' then des else '' end) as col2,
Max(Case when val = 'DCPK' then des else '' end) as col3
from
(
select *
from @tt t left outer join @yy y on t.com like '%' + val + '%') b group by b.id) as c

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-08 : 02:33:45

You need Normalization. Do google search on the same

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-08 : 08:07:07

pbguy, your code fails on his sample data. For instance, STWC is "like" TWC.

oahu9872,
if you are stuck with this table design, then I would advise you to write a user-defined function that accepts a single code string a returns a description string. Since you are also having to pivot the result set, a simple join will not suffice and you would have to jump through a lot of SQL hoops to do this as a single SQL statement.

e4 d5 xd5 Nf6

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-08 : 08:31:13

He was asking about the first row, so i looked into the first row(8082459328) not other rows...but this could solve, if he adds TWC in table two.

Select id, Description = (col1 + ' ' + col2 + ' ' + col3 + ' ' + col4 ) From
(Select id,
Max(Case when val = 'DAVI' then des else '' end) as col1,
max(Case when val = 'DBVI' then des else '' end) as col2,
Max(Case when val = 'DCPK' then des else '' end) as col3,
Max(Case when val = 'TWC' then des else '' end) as col4
from
(
select *
from @tt t left outer join @yy y on ',' + t.com + ',' like '%,' + val + ',%') b group by b.id) as c

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-05-08 : 10:28:06

quote:
Originally posted by pbguy

He was asking about the first row, so i looked into the first row(8082459328) not other rows...

Oh, please...
Your second code sample is an improvement, but still requires hard-coding of Feature Code values. So I'll repeat my assertion that he should create a UDF to do this.

e4 d5 xd5 Nf6

oahu9872
Posting Yak Master

112 Posts

Posted - 2007-05-08 : 13:01:38

thanks for the help. i agree, normalization or just plain common sense early on would prevent this, but it was handed to me like this, so i'm stuck with it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources