Posted by Hugo Kornelis on 10/30/07 20:22

On Tue, 30 Oct 2007 11:08:37 -0700, candide_sh@yahoo.de wrote:

>Hello,
>
>can I do this via SQL:
>
>example: tbltest has 5 rows:
>
>col1
>===
>A
>C
>F
>M
>R
>
>What I want is this:
>
>result:
>===============
>A, C, F, M, R
>
>Do I really have to go through the rows per SP? I could do this:

Hi Candide,

On SQL Server 2000, you have to iterate over the rows. Best if you can
do it client-side! (Note that there are some kludges that appear to
work, but none of them is documented and most of them have been proven
to produce incorrect results in some -possibly rare- circumstances, so
you should not rely on them).

On SQL Server 2005, you can write your own CLR user-defined aggregate
for this. Or, if the data contains no characters that have special
meaning in XML (like <, >, and &), you can "abuse" some of the official
XML related functionality, like this:

CREATE TABLE #t
(id int NOT NULL PRIMARY KEY,
txt varchar(20) NOT NULL);
INSERT INTO #t (id, txt)
SELECT 1, 'First' UNION ALL
SELECT 2, 'word' UNION ALL
SELECT 3, 'of' UNION ALL
SELECT 4, 'the' UNION ALL
SELECT 5, 'list';
go

SELECT * FROM #t ORDER BY id;

SELECT STUFF((SELECT ', ' + txt AS "text()"
FROM #t
ORDER BY id
FOR XML PATH('')), 1, 2, '') AS "Concatenated text";

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]