22,206
社区成员
发帖
与我相关
我的任务
分享
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
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
-- 建测试表
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)
*/