comma delimited values - 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 2008 Forums

Transact-SQL (2008)

comma delimited values

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-06-17 : 08:01:15

I have a table that is a varchar of comma delimiter numbers. The numbers represent counties. I was hoping I did not have to run 2 stored procs to get this. Is there a way I can bring back the county names from the one proc. Here is the email sql:


SELECT     County_Code, Email_Addresses
FROM         dbo.Report_Email
WHERE     (Report_Email_ID = 4)

It brings back:
"03,22,25" "qqq@q.com"

This sql for the counties:


SELECT     County_Code, County_Name
FROM         dbo.County_LKP
WHERE     (County_Code = '03')

This brings back
"03" "Armstrong"

So what I want to bring back is:
"Armstrong,Dauphin,Cumberland" "qqq@q.com"

Dave
Helixpoint Web Development
http://www.helixpoint.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-17 : 08:07:47

1. use fnParseList() to split the county_code
2. inner join the split code to the County_LKP table
3. use the method shown here to concatenate the county name back into csv

KH
[spoiler]Time is always against us[/spoiler]

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2011-06-17 : 08:18:17

Maybe a bit to complicated for me

Dave
Helixpoint Web Development
http://www.helixpoint.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-17 : 11:03:07

[code]
-- VERSION 1
; with Step1 as
(
SELECT County_Code, Email_Addresses, County = Data
FROM Report_Email
CROSS APPLY dbo.fnParseList(',', County_Code)
WHERE Report_Email_ID = 4
)
-- Step 2
select County_Name = stuff((select ',' + y.County_Name
from Step1 x
inner join County_LKP y on x.County = y.County_Code
where x.Email_Addresses = s1.Email_Addresses
order by x.County
for xml path('')), 1, 1, ''),
s1.Email_Addresses
from Step1 s1
group by s1.Email_Addresses

-- VERSION 2 (all together)
select County_Name = stuff(c.County_Name, 1, 1, ''), e.Email_Addresses
from Report_Email e
cross apply
(
select ',' + y.County_Name
from dbo.fnParseList(',', e.County_Code) x
inner join County_LKP y on x.Data = y.County_Code
order by x.RowID
for xml path('')
) c (County_Name)
WHERE e.Report_Email_ID = 4
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources