Do I need to do a PIVOT in this Sql Server 2012 Query? -

i'm not sure if need pivot extract data simple result set. if do, how?

context.

each location can exist in 0<->many counties. each location, display counties in same result (say .. comma seperated).

sample data

locations id   name -------------  1   street1  2   street2  3   county1  4   county2  5   neighbourhood12121  6   country4  counties locationid countyid ---------------------   1          3   1          4   2          3   5          3  eg. street1 exists inside county1 , county2 street2 exists inside county1 neighbourhood12121 exists inside county1 rest not exist in counties. 

results

i love following results:

id   name                counties -------------------------------------------------  1   street1             county1, county2  2   street2             county1  3   county1             null  4   county2             null  5   neighbourhood12121  county1  6   country4            null 

is possible sql server 2012?

to comma-separated list, i'd use stuff-for xml path('') trick:

select l.id, l.name,     stuff((select ', ' + cast(c.countyid varchar(max))             counties c             c.locationid = l.id             xml path('')), 1, 2, '') counties locations l 

sql fiddle example

note: have not provided table county names have used ids here. assume can figure out rest.

pivot useful when want multiple columns row-based data, since want single column here, don't think useful.