How to concatenate data from multiple rows into a single rows

I had a problem once. One table had a lot of data in a column on multiple rows. I had to display it in a view but I needed all the data on that column to be displayed in a single cell on a row. This view was supposed to be the data source for a report ... long story ...

Anyway, the basic idea is this:

SELECT some_column = (your select query
 for xml path(''))

this only works in sql server 2008 because earlier versions don't have the "xml" keyword.

Here is an example:

SELECT Documente = (SELECT coalesce(td.Descriere+',','') as [text()] FROM Document d 
   INNER JOIN TipDocument td ON d.TipDocumentId = td.Id  for xml path(''))