将查询结果拆成多个列解决方法

code      name     code1   name1       code2  name2------------------------------------------------------8    饶平五     23    伟雄         32    亚辉9    江成       24    后面1号      33    亚中10    陈明       26    老高         34    迎满17    清水       27    818          38    炎松20    灶强       

--sql 2000create table tb(code int,name nvarchar(10))insert into tb values(8  ,N'饶平五')insert into tb values(9  ,N'江成')insert into tb values(10 ,N'陈明')insert into tb values(17 ,N'清水')insert into tb values(20 ,N'灶强')insert into tb values(23 ,N'伟雄')insert into tb values(24 ,N'后面1号')insert into tb values(26 ,N'老高')insert into tb values(27 ,N'818')insert into tb values(32 ,N'亚辉')insert into tb values(33 ,N'亚中')insert into tb values(34 ,N'迎满')insert into tb values(38 ,N'炎松')goselect max(case when (px - 1)/n.cnt = 0 then code else null end) code,       max(case when (px - 1)/n.cnt = 0 then name else null end) name,       max(case when (px - 1)/n.cnt = 1 then code else null end) code1,       max(case when (px - 1)/n.cnt = 1 then name else null end) name1,       max(case when (px - 1)/n.cnt = 2 then code else null end) code2,       max(case when (px - 1)/n.cnt = 2 then name else null end) name2from(  select t.* , px = (select count(1) from tb where code < t.code) + 1 from tb t) m , (select case when count(1)%3 = 0 then count(1)/3 else count(1)/3 + 1 end cnt from tb ) ngroup by (px - 1)%n.cntdrop table tb/*code        name       code1       name1      code2       name2      ----------- ---------- ----------- ---------- ----------- ---------- 8           饶平五        23          伟雄         33          亚中9           江成         24          后面1号       34          迎满10          陈明         26          老高         38          炎松17          清水         27          818        NULL        NULL20          灶强         32          亚辉         NULL        NULL（所影响的行数为 5 行）*/--sql 2005create table tb(code int,name nvarchar(10))insert into tb values(8  ,N'饶平五')insert into tb values(9  ,N'江成')insert into tb values(10 ,N'陈明')insert into tb values(17 ,N'清水')insert into tb values(20 ,N'灶强')insert into tb values(23 ,N'伟雄')insert into tb values(24 ,N'后面1号')insert into tb values(26 ,N'老高')insert into tb values(27 ,N'818')insert into tb values(32 ,N'亚辉')insert into tb values(33 ,N'亚中')insert into tb values(34 ,N'迎满')insert into tb values(38 ,N'炎松')goselect max(case when (px - 1)/n.cnt = 0 then code else null end) code,       max(case when (px - 1)/n.cnt = 0 then name else null end) name,       max(case when (px - 1)/n.cnt = 1 then code else null end) code1,       max(case when (px - 1)/n.cnt = 1 then name else null end) name1,       max(case when (px - 1)/n.cnt = 2 then code else null end) code2,       max(case when (px - 1)/n.cnt = 2 then name else null end) name2from(  select t.* , px = row_number() over(order by code) from tb t) m , (select case when count(1)%3 = 0 then count(1)/3 else count(1)/3 + 1 end cnt from tb ) ngroup by (px - 1)%n.cntdrop table tb/*code        name       code1       name1      code2       name2----------- ---------- ----------- ---------- ----------- ----------8           饶平五        23          伟雄         33          亚中9           江成         24          后面1号       34          迎满10          陈明         26          老高         38          炎松17          清水         27          818        NULL        NULL20          灶强         32          亚辉         NULL        NULL警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/