SQL多行内容合成一个字段

[eaglexie]

我的2张表，表A01,如下
DOC_NO REMARK
2014073001
2014073002
2014073003
表A02,如下
DOC_NO SequenceNumber LOT
2014073001 1 AA
2014073001 2 BB
2014073002 1 AS
2014073002 2 AD
2014073002 3 AF
2014073003 1 QW

我现在想要的结果是
DOC_NO REMARK
2014073001 AA；BB
2014073002 AS；AD；AF
2014073003 QW

这个用语句如何实现？

[DBA_Huangzj]

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-31 07:43:05
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A1]
if object_id('[A1]') is not null drop table [A1]
go 
create table [A1]([DOC_NO] int,[REMARK] varchar(20))
insert [A1]
select 2014073001,null union all
select 2014073002,null union all
select 2014073003,null
--> 测试数据：[A2]
if object_id('[A2]') is not null drop table [A2]
go 
create table [A2]([DOC_NO] int,[SequenceNumber] int,[LOT] varchar(2))
insert [A2]
select 2014073001,1,'AA' union all
select 2014073001,2,'BB' union all
select 2014073002,1,'AS' union all
select 2014073002,2,'AD' union all
select 2014073002,3,'AF' union all
select 2014073003,1,'QW'
--------------开始查询--------------------------

UPDATE [A1]
SET [REMARK]=[LOT]
FROM (select a.[DOC_NO],
stuff((select ';'+[LOT] from [A2] b 
       where b.[DOC_NO]=a.[DOC_NO]
       for xml path('')),1,1,'') [LOT]
from [A2] a
group by  a.[DOC_NO])[A2]
WHERE a1.[DOC_NO]=a2.[DOC_NO]
go
select * from [A1]
----------------结果----------------------------
/* 
DOC_NO      REMARK
----------- --------------------
2014073001  AA;BB
2014073002  AS;AD;AF
2014073003  QW
*/

[xzb001]

with T1 as ( select doc_no , lot1 = max(case sequenceNumber when 1 then lot end) , lot2 = max(case sequenceNumber when 2 then lot end) , lot3 = max(case sequenceNumber when 3 then lot end) -- 如果超过3个，这里就需要按个数增加列的数量 from A02 group by doc_no ) , T2 as ( select doc_no , remark = isnull(lot1+';','') + isnull(lot2+';','') + isnull(lot3,'') from T1 ) select doc_no , remark = case when right(remark,1) = ';' then left(remark,len(remark)-1) else remark end from T2

[TIANTANGDEGEZI]

create table A01
(DOC_No char(20),
Remark char(100))

create table A02
(DOC_No char(20),
SequenceNumber int,
Lot char(20))

insert into A01 values
('2014073001',''),('2014073002',''),('2014073003','')

insert into A02 values
('2014073001',1,'AA'),('2014073001',2,'BB'),
('2014073002',1,'AS'),('2014073002',2,'AD'),
('2014073002',3,'AF'),('2014073003',1,'QW')

select DOC_No,LEFT(LotList,LEN(LotList)-1) as remark from(
select DOC_No,(select rtrim(Lot)+'；' from A02
where DOC_No=a.DOC_No for XML path('')) as LotList
 from A02 a group by DOC_No)b

for xml的用法参见博文：http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html