27,579
社区成员
发帖
与我相关
我的任务
分享
declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename(row) from t4 group by row
exec('select * from t pivot (max(val) for row in('+@s+'))b')
-----------------------------------------------------------------
(9 行受影响)
col 1 2 3 4 5 6 7 8 9
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1x1= 1 1x2= 2 1x3= 3 1x4= 4 1x5= 5 1x6= 6 1x7= 7 1x8= 8 1x9= 9
2 NULL 2x2= 4 2x3= 6 2x4= 8 2x5=10 2x6=12 2x7=14 2x8=16 2x9=18
3 NULL NULL 3x3= 9 3x4=12 3x5=15 3x6=18 3x7=21 3x8=24 3x9=27
4 NULL NULL NULL 4x4=16 4x5=20 4x6=24 4x7=28 4x8=32 4x9=36
5 NULL NULL NULL NULL 5x5=25 5x6=30 5x7=35 5x8=40 5x9=45
6 NULL NULL NULL NULL NULL 6x6=36 6x7=42 6x8=48 6x9=54
7 NULL NULL NULL NULL NULL NULL 7x7=49 7x8=56 7x9=63
8 NULL NULL NULL NULL NULL NULL NULL 8x8=64 8x9=72
9 NULL NULL NULL NULL NULL NULL NULL NULL 9x9=81
(9 行受影响)
select
max(case when col=1 then val end) [1],
max(case when col=2 then val end) [2],
max(case when col=3 then val end) [3],
max(case when col=4 then val end) [4],
max(case when col=5 then val end) [5],
max(case when col=6 then val end) [6],
max(case when col=7 then val end) [7],
max(case when col=8 then val end) [8],
max(case when col=9 then val end) [9]
from 你的表名 group by row
declare @x int
declare @y int
declare @c varchar(6000)
set @x = 1
while(@x<=9)
begin
select @y=@x,@c=''
while(@y<=9)
begin
select @c=cast(@x as varchar)+'x'+cast(@y as varchar)+'='
+(case when len(ltrim(@x*@y))>1 then '' else ' ' end)+ltrim(@x*@y)+' '
select @y=@y+1
print @c
end
select @x=@x+1
end
/****************
1x1= 1
1x2= 2
1x3= 3
1x4= 4
1x5= 5
1x6= 6
1x7= 7
1x8= 8
1x9= 9
2x2= 4
2x3= 6
2x4= 8
2x5=10
2x6=12
2x7=14
2x8=16
2x9=18
3x3= 9
3x4=12
3x5=15
3x6=18
3x7=21
3x8=24
3x9=27
4x4=16
4x5=20
4x6=24
4x7=28
4x8=32
4x9=36
5x5=25
5x6=30
5x7=35
5x8=40
5x9=45
6x6=36
6x7=42
6x8=48
6x9=54
7x7=49
7x8=56
7x9=63
8x8=64
8x9=72
9x9=81