经典实例-sqlserver2008201203231005

某表tb，一列a
a
str1
str2
str3
str4
str5
str6
str7
str8
....
想把每两行的字符串连接起来，如
a
str1str2
str3str4
str5str6
......

再求高手

最佳答案1：

create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb values('str5')
insert into tb values('str6')
insert into tb values('str7')
insert into tb values('str8')
go

select a = replace(stuff((select ',' + a from
(
  select t.* , px = (row_number() over(order by a) - 1)/2 from tb t
) m where m.px = n.px for xml path('')) , 1 , 1 , ''),',','')
from
(
  select t.* , px = (row_number() over(order by a) - 1)/2 from tb t
) n
group by px

drop table tb

/*
a
------------
str1str2
str3str4
str5str6
str7str8

(4 行受影响)
*/

 

最佳答案2：

create table tb(a varchar(10))
insert into tb values('str1')
insert into tb values('str2')
insert into tb values('str3')
insert into tb values('str4')
insert into tb values('str5')
insert into tb values('str6')
insert into tb values('str7')
insert into tb values('str8')
go

--创建函数解决：
create function dbo.f_str(@px int) returns varchar(1000)
as
begin
  declare @str varchar(1000)
  select @str = isnull(@str , '') + cast(a as varchar) from (select t.* , px = (((select count(1) from tb where a < t.a) + 1) - 1)/2 from tb t) m where px = @px
  return @str
end
go


--调用函数
select dbo.f_str(px) a from
(
  select t.* , px = (((select count(1) from tb where a < t.a) + 1) - 1)/2 from tb t
) m
group by px

drop function dbo.f_str

drop table tb

/*

a                    
----------------------
str1str2
str3str4
str5str6
str7str8

（所影响的行数为 4 行）
*/