Join characters using SET BASED APPROACH (Sql Server 2005)

Question

I have a table where the data are like

Data

 a
 b
 c

I need to write a SQL Query to bring the following output

Data

abc

Note:~ The number of characters are not constrained to 3.

I have solved using while loop. But I have to do it using set based approach.So how can I

join those characters? I tried using COALESCE but no luck

Please help me

Edit:

I cannot use any function or CLR. It needs to be entirely in 1 SQL.

FOR XML PATH is a good choice but it is showing some link. Actually I have never used that, so I have commented like this.

How to overcome the link text while using the FOR XML PATH. and how it works?

Any other way apart from FOR XML PATH?

Thanks

Answer 1

You need to use a Numbers Table

Answer 2

What you're really looking for is a string concatenation aggregate. As popular as this idea is, I'm not certain why Microsoft has yet to implement one in the language. You can, however, use CLR integration in SQL Server 2005 and above to create your own custom aggregate that will do just that. You would then be able to do something like...

select
    concat(your_column)

from your_table

If you're going to be using this a lot or will need more granular control (such as being able to specify a delimiter, etc.) then a CLR approach is worth the effort. If all you need is just straight concatenation, then the for path trick is probably easier.

Answer 3

Just need to get the XML PATH bit correct...

CREATE TABLE #foo (bar char(1))

INSERT #foo SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'
GO

SELECT
    buzz
FROM
    (
    SELECT 
        CAST(bar AS varchar(8000))
    FROM 
        #foo
    FOR XML PATH ('')
    ) fizz(buzz)

DROP TABLE #foo