nicolay.tchernitsky (10/13/2009)

---

the XML solution can also be:

select replace(
convert(
varchar(max),
(select name+',' as [text()] from fruit order by id for xml path(''))
)+',,,'
,',,,,'
,'')

or, if there is no need in deleting the last comma and converting the result from xml to string, just

select name+',' as [text()] from fruit order by id for xml path('')

with   
fruit as (
  select 'Apple'  as name, 101 as id UNION ALL
  select 'Banana' as name, 102 as id UNION ALL
  select 'Orange' as name, 103 as id UNION ALL
  select 'Melon'  as name, 104 as id UNION ALL
  select 'Grape'  as name, 105 as id 
)
SELECT STUFF((select ','+name from fruit order by id for xml path('')),1,1,'') AS CSV