sql ¼òµ¥²éѯÇóÖú - MS-SQL Server / ÒÉÄÑÎÊÌâ

Êý¾ÝÄÚÈÝ

no name
-----------
x1 wade
2d james
pk kobe
aa KG


Ï£Íû½á¹ûΪ


no name no name
-------------------------
x1 wade 2d james
px kobe aa KG

---

SQL code:
create table tb (no varchar(5),name varchar(10))
insert tb
select 'x1', 'wade' union all
select '2d', 'james' union all
select 'pk', 'kobe' union all
select 'aa', 'KG'

select max(no1) no ,max(name1) name,max(no2) no ,max(name2) name from
(
select (row_number()over(order by getdate())-1)/2 rn,
(case when row_number()over(order by getdate())%2=1 then no else '' end) no1,
(case when row_number()over(order by getdate())%2=1 then name else '' end) name1,
(case when row_number()over(order by getdate())%2=0 then no else '' end) no2,
(case when row_number()over(order by getdate())%2=0 then name else '' end) name2
from tb
) t
group by rn

/*
no name no name
----- ---------- ----- ----------
x1 wade 2d james
pk kobe aa KG

£¨ËùÓ°ÏìµÄÐÐÊýΪ 2 ÐУ©

*/

---

¾ßÌåÀïÃæÓжàÉټǼÊÇδ֪µÄ

---

ѧϰÖÐ

---

ÒýÓÃ

¾ßÌåÀïÃæÓжàÉټǼÊÇδ֪µÄ

ÄÇЩ²âÊÔÓï¾äÄã²»ÓùÜ.Ö»ÐèÒªÕâ3¾ä:

select * , id=identity(int,1,1) into tmp from tb