returning one single record rather than multiples

Hi

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'?

Thanks
LeeLee (lee@.digital-interactive.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi

I've just tried that (in SQL 2005)

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

and its returns errors, is that a working example?

Thanks
Lee|||The example from Erland is good, he was just sketching quickly to give you
an idea of how to solve your problem (and something to work on, since there
was no DDL posted). Here is a copy and paste extension to that:

CREATE TABLE tbl (col char(1))

INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')
INSERT INTO tbl VALUES ('M')

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

DROP TABLE tbl

HTH,

Plamen Ratchev
http://www.SQLStudio.com