How will you concatenate data in Sql Server without using a variable and any RBAR approach?

Answer:

Suppose, we have some data in a table as shown under

Data
----------
Hello,
How
Are
You

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

ConcatenateData
-----------------------
Hello,How Are You

Solution

DECLARE @t TABLE(Data Varchar(20))

INSERT @t SELECT 'Hello,' UNION ALL SELECT 'How' UNION ALL SELECT 'Are' UNION ALL SELECT 'You'



SELECT

		ConcatenateData

FROM

    (

		SELECT ' ' + CAST(Data AS varchar(8000))

		FROM  @t        

		FOR XML PATH ('')

    ) X(ConcatenateData)