Convert Row Values From Table into Concatenated String

-- -----------------------------------
-- Example
-- -----------------------------------

--
-- Create dummy table:
create table dbo.ConcatenationDemo(
    RowID int primary key not null,
    Txt varchar(max) not null
);

--
-- Insert dummy records:
insert into dbo.ConcatenationDemo (RowID, Txt)
    select 1, 'A1'
    union
    select 2, 'A2'
    union
    select 3, 'A3'
    union
    select 4, 'A4'
    union
    select 5, 'A5'
    union
    select 6, 'A6'
    union
    select 7, 'A7'
    union
    select 8, 'A8';

--
-- Using FOR XML PATH to generate row values from a table, into a single
-- concatenated string
--
-- Some of the XML statements introduced in SQL Server 2005 had to implement a
-- means of looping around data in order to produce XML. This solution takes
-- advantage of this, but strips out the XML specific parts to produce the comma
-- separated list.
select STUFF(
    (
        select ',' + Txt
        from dbo.ConcatenationDemo
        for XML path('')
    ), 1, 1, ''
) as Txt;

-- 
-- Other example...
select distinct f.FacilityPaProcessGroup,
    (select STUFF (
        (
            -- Concatenate all FacilityUnitNumber's for PA Group into one CSV field:
            select distinct ',' + cast(s.FacilityUnitNumber as nvarchar(5))
            from dbo.Facility as s
            where s.FacilityActive = 1
            and f.FacilityPaProcessGroup = s.FacilityPaProcessGroup
            for xml path('')), 1, 1, ''
        )
    ) as FacilityUnitNumberList
from dbo.Facility as f
where f.FacilityActive = 1;