select max(case (px-1)%2 when 0 then 名称 else '' end) 名称1, max(case (px-1)%2 when 1 then 名称 else '' end) 名称2 from ( select 名称 , px = (select count(1) from tb where 名称 < t.名称) + 1 from tb t ) m group by (px-1)/2
create table tb(名称 varchar(10)) insert into tb values('A3A') insert into tb values('B3B') insert into tb values('B3C') insert into tb values('DCD') insert into tb values('A3B') goselect max(case (px-1)%2 when 0 then 名称 else '' end) 名称1, max(case (px-1)%2 when 1 then 名称 else '' end) 名称2 from ( select 名称 , px = (select count(1) from tb where 名称 < t.名称) + 1 from tb t ) m group by (px-1)/2drop table tb/* 名称1 名称2 ---------- ---------- A3A A3B B3B B3C DCD (所影响的行数为 3 行) */
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (name varchar(3)) insert into #T select 'A3A' union all select 'B3B' union all select 'B3C' union all select 'DCD' union all select 'A3B';with T as (select id=row_number()over(order by name),name from #T) select a.name name1, b.name name2 from (select * from T where id%2=1) a full join ((select * from T where id%2=0)) b on a.id=b.id-1/* name1 name2 ----- ----- A3A A3B B3B B3C DCD NULL */
create table #temp(id int identity(1,1),ctext nvarchar(20)) insert into #temp select 'A3A' union all select 'B3B' union all select 'B3C' union all select 'DCD' union all select 'A3B' select * from #tempselect max(case id%2 when 1 then ctext else '' end) as col, max(case id%2 when 0 then ctext else '' end) as col2 from #temp group by (id-1)/2 /* col col2 -------------------- -------------------- A3A B3B B3C DCD A3B (3 row(s) affected) */
if object_id('tb') is not null drop table tb go create table tb(名称 varchar(10)) insert into tb values('A3A') insert into tb values('B3B') insert into tb values('B3C') insert into tb values('DCD') insert into tb values('A3B') select *,identity(int,0,1) as id into #1 from tb declare @t table(名称1 varchar(10),名称2 varchar(10)) insert into @t select max(case when a.id%2=0 then 名称 else null end),max(case when a.id%2=1 then 名称 else null end) from #1 a group by (a.id)/2 select * from @t drop table #1 -- 接分
select 名称1,名称2 from ( select row_number() over(order by 名称) /2 as f_no,case when row_number() over(order by 名称) % 2 = 1 then 名称 else null end as 名称1 from 表 )as a inner join ( select row_number() over(order by 名称) /2 as f_no,case when row_number() over(order by 名称) % 2 = 0 then 名称 else null end as 名称2 from 表 ) as b on a.f_no = b.f_no where 名称1 is not null and 名称2 is not null
max(case (px-1)%2 when 0 then 名称 else '' end) 名称1,
max(case (px-1)%2 when 1 then 名称 else '' end) 名称2
from
(
select 名称 , px = (select count(1) from tb where 名称 < t.名称) + 1 from tb t
) m
group by (px-1)/2
insert into tb values('A3A')
insert into tb values('B3B')
insert into tb values('B3C')
insert into tb values('DCD')
insert into tb values('A3B')
goselect
max(case (px-1)%2 when 0 then 名称 else '' end) 名称1,
max(case (px-1)%2 when 1 then 名称 else '' end) 名称2
from
(
select 名称 , px = (select count(1) from tb where 名称 < t.名称) + 1 from tb t
) m
group by (px-1)/2drop table tb/*
名称1 名称2
---------- ----------
A3A A3B
B3B B3C
DCD (所影响的行数为 3 行)
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (name varchar(3))
insert into #T
select 'A3A' union all
select 'B3B' union all
select 'B3C' union all
select 'DCD' union all
select 'A3B';with T as (select id=row_number()over(order by name),name from #T)
select a.name name1, b.name name2 from (select * from T where id%2=1) a full join ((select * from T where id%2=0)) b on a.id=b.id-1/*
name1 name2
----- -----
A3A A3B
B3B B3C
DCD NULL
*/
create table #temp(id int identity(1,1),ctext nvarchar(20))
insert into #temp
select 'A3A'
union all select 'B3B'
union all select 'B3C'
union all select 'DCD'
union all select 'A3B' select * from #tempselect max(case id%2 when 1 then ctext else '' end) as col,
max(case id%2 when 0 then ctext else '' end) as col2
from #temp
group by (id-1)/2
/*
col col2
-------------------- --------------------
A3A B3B
B3C DCD
A3B (3 row(s) affected)
*/
drop table tb
go
create table tb(名称 varchar(10))
insert into tb values('A3A')
insert into tb values('B3B')
insert into tb values('B3C')
insert into tb values('DCD')
insert into tb values('A3B') select *,identity(int,0,1) as id into #1 from tb
declare @t table(名称1 varchar(10),名称2 varchar(10))
insert into @t
select max(case when a.id%2=0 then 名称 else null end),max(case when a.id%2=1 then 名称 else null end)
from #1 a
group by (a.id)/2
select * from @t
drop table #1
--
接分
select 名称1,名称2
from
(
select row_number() over(order by 名称) /2 as f_no,case when row_number() over(order by 名称) % 2 = 1 then 名称 else null end as 名称1 from 表
)as a
inner join
(
select row_number() over(order by 名称) /2 as f_no,case when row_number() over(order by 名称) % 2 = 0 then 名称 else null end as 名称2 from 表
) as b
on a.f_no = b.f_no
where 名称1 is not null and 名称2 is not null