sql - How to convert columns data in comma separated list -

i in little strange situation. third party application front end doesn't allow user procedures or udf/scalar functions called.

only option write sql , below how data looks. first pic has mistake last pid suppose 1 , second last 2.

what need this,

this can done using udf/cursor in advantage database server 9 don't have choice. don't know if possible. in sybase there exist function called list sort of work not sure here.

application allow call built in functions.

declare @table table (    pid integer,    medicine varchar(10)  )  insert @table values (1, 'abc') insert @table values (2, 'abc') insert @table values (1, 'def') insert @table values (2, 'def') insert @table values (1, 'ghi')    select distinct a.pid,                 medicine = stuff((select ',' + b.medicine                                     @table b                                     a.pid = b.pid                                       xml path('')),1,1,'') @table 

result:

**pid   medicine** 1   abc,def,ghi 2   abc,def