Need to return a comma separated value based on two separate data points

Question

I have data in a table that looks like this (we'll call it PersonServiceArea for this exercise):

PersonKey ServiceAreaState ServiceAreaCounties

30076 DC * <- Since no counties exist here, this is how we treat the County field

30076 VA ARLINGTON

30076 VA BEDFORD

30076 VA ETC

12345 PA BUCKS

12345 PA CHESTER

12345 PA MONTGOMERY

What I need to do is write something that pulls it together in this format:

PersonKey ServiceAreaState ServiceAreaCounties

30076 DC *

30076 VA ARLINGTON,BEDFORD,ETC

12345 PA BUCKS,CHESTER,MONTGOMERY

However, all I keep coming up with is:

PersonKey ServiceAreaState ServiceAreaCounties

30076 DC *

30076 DC ALEXANDRIA,CITY,ARLINGTON,BEDFORD

30076 DC ALLEGANY,ANNE,ARUNDEL,BALTIMORE

30076 DC BERKELEY,JEFFERSON,MORGAN

30076 DC BRUNSWICK,DUPLIN,NEW,HANOVER

As you can see, my issue is setting up my query so that, for each PersonKey and each individual ServiceAreaState they work in, I only get results back FOR THAT STATE, and not a repeating mess of every county they service under every state they service. Also, counties that consist of two words end up with a comma delimiter as well--how can I avoid this?

For the record, working on this in SQL Server 2008. Any and all help to avoid setting up 100 temporary tables to do the job on a state-by-state basis would be much appreciated!

Answer 1

What you need is "group_concat" (which is available in mysql but not sql server) Nevertheless, take a look at Simulating group_concat MySQL function in Microsoft SQL Server 2005?

Answer 2

I was able to do it using recursive cte

select 30076 PersonKey, 'DC' ServiceAreaState, '*' ServiceAreaCounties
into #a
union
select 30076 PersonKey, 'VA' ServiceAreaState, 'ARLINGTON' ServiceAreaCounties
union 
select 30076 PersonKey, 'VA' ServiceAreaState, 'BEDFORD' ServiceAreaCounties
union
select 30076 PersonKey, 'VA' ServiceAreaState, 'ETC' ServiceAreaCounties
union
select 12345 PersonKey, 'PA' ServiceAreaState, 'BUCKS' ServiceAreaCounties
union
select 12345 PersonKey, 'PA' ServiceAreaState, 'CHESTER' ServiceAreaCounties
union 
select 12345 PersonKey, 'PA' ServiceAreaState, 'MONTGOMERY' ServiceAreaCounties


;with cte as
(
select PersonKey, ServiceAreaState, cast(ServiceAreaCounties as varchar(100)) ServiceAreaCounties, rown from (select PersonKey, ServiceAreaState, ServiceAreaCounties, row_number() over (partition by cast(PersonKey as varchar(10))+ ServiceAreaState  order by (select null))rown from #a)A where rown = 1
union all
select A.PersonKey, A.ServiceAreaState, cast((A.ServiceAreaCounties + ' '+ cte.ServiceAreaCounties) as varchar(100)) ServiceAreaCounties, A.rown from (select PersonKey, ServiceAreaState, ServiceAreaCounties, row_number() over (partition by cast(PersonKey as varchar(10)) + ServiceAreaState  order by (select null))rown from #a)A JOIN CTE on cte.rown + 1 = A.rown and cte.PersonKey = A.PersonKey and cte.ServiceAreaState = A.ServiceAreaState
)

select PersonKey, ServiceAreaState, ServiceAreaCounties ServiceAreaCountiesConcatenated from (
select PersonKey, ServiceAreaState, ServiceAreaCounties, rown, row_number() over (partition by cast(PersonKey as varchar(10)) + ServiceAreaState order by rown desc)newrown from cte)A where newrown = 1

RESULTS