22,300
社区成员




--> --> (Roy)生成測試數據
if not object_id('Tempdb..#a') is null
drop table #a
Go
Create table #a([Col] nvarchar(4))
Insert #a
select N'str1' union all
select N'str2' union all
select N'str3' union all
select N'str4' union all
select N'str5' union all
select N'str6' union all
select N'str7' union all
select N'str8'
Go
;with a
as
(
Select *,ROW_NUMBER()over(order by getdate()) as row from #a
)
select
a.[Col]+ISNULL(b.[Col],'') as Str1
from a
left join a as b on a.row=b.row-1
where a.row%2=1
/*
str1str2
str3str4
str5str6
str7str8
*/
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 行受影响)
*/
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 行)
*/