这是形成使用柱从与具有相同ID的行表中的字符串值的最佳方式？(Which is the best w

我使用SQL Server 2008。

这是我的表。

表A

 Aid int pk
 col2
 col3
 XYZID int

表B

 Bid int pk
 XYZID int
 Col2
 Col3 
 Col4
 seq -- (Sequence )

Table B会是这样

seq   col2    |XYZID|Bid |col3| col4 
===============================================
  1   foo     | 1   |  1 | 12 | wqw
  3   bar     | 1   | 10 | 77 | kikk
  2   foobar  | 1   |  2 |  w | ed
  1   barfoo  | 2   |  4 |  e | dwe
  2   asdsad  | 2   |  5 |  e | e

Table A是主表和基于所述XYZID在甲

我需要生成使用列从一个字符串值TableB与XYZID and Seq 。

我需要生成使用列从XYZID和序列表B的字符串值。

为例如：xyzid = 1

我预计： foo-12-wqw#foobar-w-ed#bar-77-kikk

基于Sequence foo-1,foobar-2,bar-3

Answer 1:

对于XYZID = 1 。

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB
              where XYZID = 1
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')

对于TableA中的所有行：

select stuff((select '#'+col2+'-'+col3+'-'+col4
              from TableB as B
              where A.XYZID = B.XYZID
              order by seq
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '')
from TableA as A

Answer 2:

DDL：

drop table tblx;
create table tblx
(
seq int,
col2 varchar(50),
xyzid int,
bid int,
col3 varchar(50),
col4 varchar(50)
);

数据：

insert into tblx(seq,col2,xyzid,bid,col3,col4) values
(1,   'foo'     , 1,   1,  '12', 'wqw'),
(3,   'bar'     , 1,   10, '77', 'kikk'),
(2,   'foobar'  , 1,   2,  'w',  'ed'),
(1,   'barfoo'  , 2,   4,  'e',  'dwe'),
(2,   'asdsad'  , 2,   5,  'e',  'e');

使用CTE的方法：

with a(xyzid, seq, x) as
(
select xyzid, seq, cast(col2 + '-' + col3 + '-' + col4 as varchar(max)) as x
from tblx
where seq = 1
union all
select t.xyzid, t.seq, a.x + '#' + (t.col2 + '-' + t.col3 + '-' + t.col4)
from tblx t
join a on a.xyzid = t.xyzid and t.seq = a.seq + 1
)
select xyzid, rtrim(x) as x 
from a w
where seq = (select MAX(seq) from a where xyzid = w.xyzid)
order by xyzid;

输出：

xyzid       x
----------- -----------------------------------
1           foo-12-wqw#foobar-w-ed#bar-77-kikk
2           barfoo-e-dwe#asdsad-e-e

(2 row(s) affected)

使用主表（如表一）只需要在查询一个简单的修改：

with a(xyzid, seq, x) as
(
select xyzid, seq, cast(col2 + '-' + col3 + '-' + col4 as varchar(max)) as x
from tblx
where seq = 1
union all
select t.xyzid, t.seq, a.x + '#' + (col2 + '-' + col3 + '-' + col4)
from tblx t
join a on a.xyzid = t.xyzid and t.seq = a.seq + 1
)
select w.xyzid, rtrim(x) as x 
from tblA w -- just add this main table
left join a on a.xyzid = w.xyzid 
               and seq = (select MAX(seq) from a where xyzid = w.xyzid)
order by xyzid;

数据：

create table tblA
(
aid int identity(1,1) primary key,
col2 varchar(50),
col3 varchar(50),
xyzid int
);


insert into tblA(col2,col3,xyzid) values
('','',1),
('','',2),
('','',3);

输出：

xyzid       x
----------- ------------------------------------
1           foo-12-wqw#foobar-w-ed#bar-77-kikk
2           barfoo-e-dwe#asdsad-e-e
3           NULL

(3 row(s) affected)

如果序列字段是不连续的和/或非唯一的，把一个序：

with sequencer as
(
select 
    xyzid, ROW_NUMBER() over(partition by xyzid order by seq) as seq
    , col2, col3, col4 
from tblx 
)
,a(xyzid, seq, x) as
(
select xyzid, seq, cast(col2 + '-' + col3 + '-' + col4 as varchar(max)) as x
from sequencer
where seq = 1
union all
select t.xyzid, t.seq, a.x + '#' + (col2 + '-' + col3 + '-' + col4)
from sequencer t
join a on a.xyzid = t.xyzid and t.seq = a.seq + 1
)
select w.xyzid, rtrim(x) as x 
from tblA w
left join a on a.xyzid = w.xyzid 
            and seq = (select MAX(seq) from a where xyzid = w.xyzid)
order by xyzid;

非样本连续序列：

insert into tblx(seq,col2,xyzid,bid,col3,col4) values
(1,   'foo'     , 1,   1,  '12', 'wqw'),
(5,   'bar'     , 1,   10, '77', 'kikk'),
(3,   'foobar'  , 1,   2,  'w',  'ed'),
(1,   'barfoo'  , 2,   4,  'e',  'dwe'),
(3,   'asdsad'  , 2,   5,  'e',  'e');

输出（仍然是相同的）：

xyzid       x
----------- --------------------------------------
1           foo-12-wqw#foobar-w-ed#bar-77-kikk
2           barfoo-e-dwe#asdsad-e-e
3           NULL

(3 row(s) affected)

至于速度，它仍然快。 CTE查询的成本是对XML的方法，这是95％，5％