This is just came up while playing with CTE in SQL Server, and here we get something to concatenate field values in one string.
;WITH ABC (FId, FName) AS
(
SELECT 1, CAST('' AS VARCHAR(8000))
UNION ALL
SELECT B.FId + 1, B.FName + A.FName + ', '
FROM (SELECT Row_Number() OVER (ORDER BY FId) AS RN, FName FROM tblTest) A
INNER JOIN ABC B ON A.RN = B.FId
)
SELECT TOP 1 FName FROM ABC ORDER BY FId DESC
And the query will return
FName
A, B, C, D, E,
For more details see my article
http://www.codeproject.com/useritems/Concatenate_Field_Values.asp on Code Project.
