求SQL解答

[qq_17536765]

有一个表 只有一列 result 如下：99剩法
result
1*1=1
1*2=2
1*3=3
-----
.......
1*9=9
2*1=2
2*2=4
.....
9*9=81
共81行
现在我要实现把这列拆分成9列 效果如下
result1 result 2 ---- result 9
1*1=1 2*1=2
1*2=2 ....
...... ......
1*9=9 2*9=18

[唐诗三百首]

-- 建测试表
create table #t(result varchar(20))

-- 插入测试数据
declare @x int,@y int
select @x=1
while(@x<=9)
begin
 select @y=1
 while(@y<=9)
 begin
  insert into #t(result) select rtrim(@x)+'*'+rtrim(@y)+'='+rtrim(@x*@y)
  select @y=@y+1
 end 
 select @x=@x+1
end


-- 结果
select * from #t

/*
result
--------------------
1*1=1
1*2=2
1*3=3
1*4=4
1*5=5
1*6=6
1*7=7
1*8=8
1*9=9
2*1=2
2*2=4
2*3=6
2*4=8
2*5=10
2*6=12
2*7=14
2*8=16
2*9=18
3*1=3
3*2=6
3*3=9
3*4=12
3*5=15
3*6=18
3*7=21
3*8=24
3*9=27
4*1=4
4*2=8
4*3=12
4*4=16
4*5=20
4*6=24
4*7=28
4*8=32
4*9=36
5*1=5
5*2=10
5*3=15
5*4=20
5*5=25
5*6=30
5*7=35
5*8=40
5*9=45
6*1=6
6*2=12
6*3=18
6*4=24
6*5=30
6*6=36
6*7=42
6*8=48
6*9=54
7*1=7
7*2=14
7*3=21
7*4=28
7*5=35
7*6=42
7*7=49
7*8=56
7*9=63
8*1=8
8*2=16
8*3=24
8*4=32
8*5=40
8*6=48
8*7=56
8*8=64
8*9=72
9*1=9
9*2=18
9*3=27
9*4=36
9*5=45
9*6=54
9*7=63
9*8=72
9*9=81

(81 row(s) affected)
*/


-- 拆分成9列
declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
            +'max(case when left(result,1)='''+rtrim(number)+''' and substring(result,3,1)=substring(result,3,1) 
                       then result else '''' end) ''result'+rtrim(number)+''' '
 from master.dbo.spt_values
 where type='P' and number between 1 and 9
 
select @tsql='select '+@tsql 
            +' from #t '
            +' group by substring(result,3,1) '

exec(@tsql)

/*
result1              result2              result3              result4              result5              result6              result7              result8              result9
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1*1=1                2*1=2                3*1=3                4*1=4                5*1=5                6*1=6                7*1=7                8*1=8                9*1=9
1*2=2                2*2=4                3*2=6                4*2=8                5*2=10               6*2=12               7*2=14               8*2=16               9*2=18
1*3=3                2*3=6                3*3=9                4*3=12               5*3=15               6*3=18               7*3=21               8*3=24               9*3=27
1*4=4                2*4=8                3*4=12               4*4=16               5*4=20               6*4=24               7*4=28               8*4=32               9*4=36
1*5=5                2*5=10               3*5=15               4*5=20               5*5=25               6*5=30               7*5=35               8*5=40               9*5=45
1*6=6                2*6=12               3*6=18               4*6=24               5*6=30               6*6=36               7*6=42               8*6=48               9*6=54
1*7=7                2*7=14               3*7=21               4*7=28               5*7=35               6*7=42               7*7=49               8*7=56               9*7=63
1*8=8                2*8=16               3*8=24               4*8=32               5*8=40               6*8=48               7*8=56               8*8=64               9*8=72
1*9=9                2*9=18               3*9=27               4*9=36               5*9=45               6*9=54               7*9=63               8*9=72               9*9=81

(9 row(s) affected)
*/

[极品老土豆]

select case unicode(result) when unicode(1) then result end as result1, case unicode(result) when unicode(2) then result end as result2, case unicode(result) when unicode(3) then result end as result3, case unicode(result) when unicode(4) then result end as result4, case unicode(result) when unicode(5) then result end as result5, case unicode(result) when unicode(6) then result end as result6, case unicode(result) when unicode(7) then result end as result7, case unicode(result) when unicode(8) then result end as result8, case unicode(result) when unicode(9) then result end as result9, from tablename

[lzw_0736]

WITH a1 AS
(
SELECT *,'result'+RTRIM(ROW_NUMBER() OVER(PARTITION BY right(result,1) ORDER BY result)) RE,right(result,1) re2
FROM tb
)
select result1,result2,result3,result4,result5,result6,result7,result8,result9
from a1 a 
pivot (max(result) for re in (result1,result2,result3,result4,result5,result6,result7,result8,result9)) b

[Dear SQL(燊)]

WITH LIST AS(
	select NUMBER from MASTER.[dbo].[spt_values] where [type]='P' AND NUMBER>=1 AND NUMBER<=9
)
SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9]
FROM (
	SELECT RID=ROW_NUMBER()OVER(PARTITION BY L1.NUMBER ORDER BY L2.NUMBER)+(L1.NUMBER-1),RNK=DENSE_RANK()OVER( ORDER BY L1.NUMBER),
		COL=CAST(L1.NUMBER AS NVARCHAR(20))+'*'+CAST(L2.NUMBER AS NVARCHAR(20))+'='+CAST(L1.NUMBER*L2.NUMBER AS NVARCHAR(20))
	FROM LIST L1,LIST L2
	WHERE L1.NUMBER<=L2.NUMBER
	)LI PIVOT(MAX(COL) FOR RNK IN ([1],[2],[3],[4],[5],[6],[7],[8],[9]))PV