SQL多行内容合成一个字段,该如何处理
SQL多行内容合成一个字段
我的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
这个用语句如何实现?
------解决思路----------------------
------解决思路----------------------
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
------解决思路----------------------
for xml的用法参见博文:http://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html
我的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
这个用语句如何实现?
------解决思路----------------------
----------------------------------------------------------------
-- 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
*/
------解决思路----------------------
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
------解决思路----------------------
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