Posted by Erland Sommarskog on 02/12/07 22:51

Lee (lee@digital-interactive.com) writes:
> Is it possible to return the results of a query so that instead of
> having say 10 rows its concatenated, eg
>
> My query returns 'M' 10 times, can this be returned as 'M M M M M M M
> M M M'?

In SQL 2005:

SELECT subtring(Ms, 1, datalength(Ms) / 2 - 1
FROM (SELECT col + ' ' AS [text()]
FROM tbl
FOR XML PATH('')) AS T(Ms)

This makes use of the XML functionality, and has the drawback that some
characters will be encoded.

In SQL 2000, you are unfortunately best of with a cursor.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]