1

I have a couple thousand franchises, and about 3 or 4 service groups. The service groups are in a separate table, then i have third table joining them together. A franchise may be connected to 1 or many service groups. What I am trying to achieve is listing out the franchises, with a column labeled "Service Categories". It would then be a comma separated list of which services they provide. So right now, here's my output:

id  Service Groups     name                    email                address      City         State
1   Cleaning Services  Franchise of LocationX  example@example.com  123 Fake st. Springfield  TheOneTheSimpsonsLiveIn
2   Disaster Services  Franchise of LocationX  example@example.com  123 Fake st. Springfield  TheOneTheSimpsonsLiveIn

I would like to have it so it becomes this:

id  Service Groups                        name                    email                address      City         State
1   Cleaning Services, Disaster Services  Franchise of LocationX  example@example.com  123 Fake st. Springfield  TheOneTheSimpsonsLiveIn

Any suggestions or references of methods/functions I can use to do this would be greately appreciated. Thanks!

agmcleod
  • 13,321
  • 13
  • 57
  • 96
  • You may take a look at this http://stackoverflow.com/questions/6344950/sql-query-to-get-aggregated-result-in-comma-seperators-along-with-group-by-column/6349132#6349132 – EricZ Jun 27 '11 at 13:16
  • Or this one: http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql – ypercubeᵀᴹ Jun 27 '11 at 13:29
  • possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) – Andriy M Jun 27 '11 at 14:27
  • @ypercube That post helped me out. Thanks. Post your answer and ill accept. – agmcleod Jun 27 '11 at 14:36
  • No need for that. You can post your solution as answer (and accept it) if you feel it may help others. – ypercubeᵀᴹ Jun 27 '11 at 15:46
  • Refer this post [http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/](http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/) – Madhivanan Jun 27 '11 at 13:18

1 Answers1

1

try this 

Declare @TempServiceGroups varchar(max)=''
Select COALESCE(@TempServiceGroups,'')+ServiceGroups+',' from MyTable
Select @TempServiceGroups AS ServiceGroups , Name,Email,Address,City,State
from MyTable
Amir Ismail
  • 3,865
  • 3
  • 20
  • 33