Building a String From Multiple Records

In SQL Server, there are multiple ways to build up a string from values distributed in multiple record.

Preparation

Create table #m (id int identity,c1 varchar(200))
GO
create table #c (id int,c1 varchar(20))
GO
insert into #m(c1) values(1)
 insert into #c(id, c1) values
(1,'1,2'),
(1,'3,4,5'),
(1,'6,7,8')
GO

Method 1, using variable and SELECT statement.

Declare @v1 varchar(200)
SET @v1=''
SELECT @v1 = @v1 + c1 +';'
FROM #c
PRINT @v1

Method 2, a little bit XML and string operation, if you are still using SQL Server 2000.

The good of FOR XML is it returns result in one XML document, which can be treated as a string. Soemtimes it's handy and useful when you want to display master records with result from child tables.

This is also an example on when you want to use sub-query in your statement.
(
Why the hell they invent so many names for a query used in a query(embeded query)?

SELECT list --> Sub-query
FROM --> Derived table
WHERE --> Correlated sub-query.
)

select m.id,
replace(replace(replace(
(select c1 from #c c where c.id=m.id FOR XML AUTO),
'',';')
from #m m
where m.id=1

When this technique is used in update statement, it sometimes has advantages over the other ways such as using cursor and loop because of its conciseness.

Friends using Oracle? use LISTAGG function to have a single row output from multiple rows operated.