极难的SQL转换问题（求SQL高手）！！！！！！！！
如何将SQL语句select f1 from A order by id asc的查询结果列转行。（详情如下：）
f1
-----------
a
b
c
d
e
f
g
h
i
j
转换后：
f1 f2 f3
a b c
d e f
g h i
j

[解决办法]

--create testf1 table
create table testf1(f1 char(1))
---Insert data
insert into testf1 values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j');
--Create destination table
create table testf2(f1 char(1),f2 char(1),f3 char(1))
--temp table
create table #testf2(f1 char(1),f2 char(1),f3 char(1),ID int)

select ROW_NUMBER() over(order by f1)%3 as [余数],ROW_NUMBER() over(order by f1) as [Num],f1 into #test1 from testf1

insert into #testf2(f1,ID) select f1,Num from #test1 where [余数] = 1

--更新f2 column
update #testf2 set f2 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +1
--更新f3 column
update #testf2 set f3 = t1.f1 from #testf2 f2 inner join #test1 t1 on t1. Num = f2.ID +2
---Insert into destination table
insert into testf2
select ISNULL(f1,'') as f1, ISNULL(f2,'') as f2,ISNULL(f3,'') as f3 from #testf2

select * from testf2

f1 f2 f3
---- ---- ----
a b c
d e f
g h i
j

(4 row(s) affected)
[解决办法]

SQL code

create table testf1(f1 char(1))insert into testf1 select 'a' union allselect 'b'union allselect 'c'union allselect 'd'union allselect 'e'union allselect 'f'union allselect 'g'union allselect 'h'union allselect 'i'union allselect 'j';--sql 2005;with cte as(    select f1,rid=row_number() over (order by getdate())    from testf1)select a.f1 as af,b.f1 as bf,c.f1 as cffrom (select *,(rid-1)/3 as fg from cte where (rid-1)%3=0) a    left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=1) b on a.fg = b.fg    left join (select *,(rid-1)/3 as fg from cte where (rid-1)%3=2) c on a.fg = c.fg--sql 2000select f1,rid=identity(int,1,1) into #testf1 from testf1select a.f1 as af,b.f1 as bf,c.f1 as cffrom (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=0) a    left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=1) b on a.fg = b.fg    left join (select *,(rid-1)/3 as fg from #testf1 where (rid-1)%3=2) c on a.fg = c.fgdrop table #testf1drop table testf1/*****************************(10 行受影响)af   bf   cf---- ---- ----a    b    cd    e    fg    h    ij    NULL NULL(4 行受影响)(10 行受影响)af   bf   cf---- ---- ----a    b    cd    e    fg    h    ij    NULL NULL(4 行受影响)
[解决办法]
SQL code
DECLARE @A TABLE([F1] VARCHAR(1))INSERT @ASELECT 'A' UNION ALLSELECT 'B' UNION ALLSELECT 'C' UNION ALLSELECT 'D' UNION ALLSELECT 'E' UNION ALLSELECT 'F' UNION ALLSELECT 'G' UNION ALLSELECT 'H' UNION ALLSELECT 'I' UNION ALLSELECT 'J';WITH M1 AS(    SELECT ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) AS ID,* FROM @A),M2 AS(    SELECT         ID%3 AS RID,        ROW_NUMBER () OVER (PARTITION BY ID%3 ORDER BY ID) AS NID,    * FROM M1)SELECT     MAX(CASE WHEN RID=1 THEN F1 ELSE '' END) AS F1,    MAX(CASE WHEN RID=2 THEN F1 ELSE '' END) AS F2,    MAX(CASE WHEN RID=0 THEN F1 ELSE '' END) AS F3FROM M2 GROUP BY NID/*F1   F2   F3---- ---- ----A    B    CD    E    FG    H    IJ         */