Microsoft sql server: Pivot rows together with concatenate? -

i have select query outputs follows:

pid-------name------------------response 147------factory influenced-----fish paste 147------factory influenced-----contract packer 147------factory influenced-----cereals 147------factory influenced-----biscuits 154------factory influenced-----werda 154------factory influenced-----worcester 158------factory influenced-----moirs 158------factory influenced-----oats 158------factory influenced-----noogy 157------factory influenced-----safari nuts 158------factory influenced-----savoury foods 

what make 'factory influenced' column , 'response' fields. concatenate these values follows:

pid-----factory influenced           147-----fish paste, contract packer, cereals, biscuits           154-----werda, worcester             158-----moirs, oats, noogy, safari nuts, savoury foods   

any highly appreciated.

this process of concatenating multiple rows single column not pivot, pivot when convert rows of data columns. since want create comma-separated list of values code different.

you can use for xml path , stuff create list:

select t1.pid,   t1.name,    stuff(          (select ', ' + t2.response           yourtable t2           t1.pid = t2.pid           xml path (''))           , 1, 1, '')  response yourtable t1 group t1.pid, t1.name; 

see sql fiddle demo.

or can use cross apply for xml path:

select distinct t1.pid,   t1.name,    response = left(t2.response, len(t2.response)-1)  yourtable t1 cross apply (   select t2.response + ', '   yourtable t2   t1.pid = t2.pid   xml path('') ) t2 (response); 

see sql fiddle demo. both versions give result of:

| pid |               name |                                       response | |-----|--------------------|------------------------------------------------| | 147 | factory influenced | fish paste, contract packer, cereals, biscuits | | 154 | factory influenced |                               werda, worcester | | 157 | factory influenced |                                    safari nuts | | 158 | factory influenced |              moirs, oats, noogy, savoury foods |