SQL 2005列转行的疑难问题

[默默唐]

例如： 编号
001
002
003
004
005
006
007
008

查询得出结果显示如下：
编号1 编号2 编号3
001 004 007
002 005 008
003 006 NULL

也就是隔3行换一次列

declare @tb table([col] varchar(3))
insert @tb
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '005' union all
select '006' union all
select '007' union all
select '008''

;WITH TEMP AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY getdate()) AS RID
FROM @tb
)
SELECT 
  col1=max(case when RID%3=1 then col end),
  col2=max(case when RID%3=2 then col end),
  col3=max(case when RID%3=0 then col end)
FROM TEMP
GROUP BY (RID-1)/3

得出的结果是：
编号1 编号2 编号3
001 002 003
004 005 006
007 008 NULL

搞不定，求助各位大神~~~

[Andy__Huang]

declare @tb table([col] varchar(3))
insert @tb
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '005' union all
select '006' union all
select '007' union all
select '008'

;WITH temp AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY getdate()) AS RID
FROM @tb
),
t1 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp 
),
t2 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp where RID>(select MAX(RID) from t1)
),
t3 as
(
select top 3 *,ROW_NUMBER() OVER(ORDER BY getdate()) AS rn from temp where RID>(select MAX(RID) from t2)
)
select t1.col as 编号1,t2.col as 编号2,t3.col as 编号3 
from t1
left join t2 on t1.rn=t2.rn
left join t3 on t1.rn=t3.rn

/*
编号1	编号2	编号3
001	004	007
002	005	008
003	006	NULL
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-09-10 14:22:21
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
--	Apr  2 2010 15:53:02 
--	Copyright (c) Microsoft Corporation
--	Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([编号] varchar(3))
insert [tb]
select '001' union all
select '002' union all
select '003' union all
select '004' union all
select '005' union all
select '006' union all
select '007' union all
select '008'
--------------开始查询--------------------------
;WITH F AS
(
SELECT *,ID=ROW_NUMBER()OVER(ORDER BY GETDATE()) FROM tb
)

SELECT 
  A.编号 AS 编号1,B.编号 AS 编号2,C.编号 AS 编号3
FROM
  (SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 0) A
LEFT JOIN
  (SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 1) B
ON
  (A.ID-1)/3=(B.ID-1)/3-1
AND
  (A.ID-1)%3=(B.ID-1)%3
LEFT JOIN
  (SELECT * FROM F WHERE ((ID - 1) / 3) % 3 = 2) C
ON
  (B.ID-1)/3=(C.ID-1)/3-1
AND
  (B.ID-1)%3=(C.ID-1)%3
----------------结果----------------------------
/*编号1  编号2  编号3
---- ---- ----
001  004  007
002  005  008
003  006  NULL

(3 行受影响)



*/

[默默唐]

谢谢楼上两位高手的精彩解答，都可以解决问题。相对来讲，hdhai9451大大的更易懂一些。版主大大的也不错。