marți, 18 ianuarie 2011

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(''))

Niciun comentariu:

Trimiteți un comentariu