34,588
社区成员
发帖
与我相关
我的任务
分享
--强大的pivot
--数据准备
create table t4
(
row int,
col int,
val char(10)
)
--添加测试数据
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
insert into t4 values(@y-1,@x,@c)
end
select @x=@x+1
end
---------------------------------------------------------------------------------
row col val
----------- ----------- ----------
1 1 1x1= 1
2 1 1x2= 2
3 1 1x3= 3
4 1 1x4= 4
5 1 1x5= 5
6 1 1x6= 6
7 1 1x7= 7
8 1 1x8= 8
9 1 1x9= 9
2 2 2x2= 4
3 2 2x3= 6
4 2 2x4= 8
5 2 2x5=10
6 2 2x6=12
7 2 2x7=14
8 2 2x8=16
9 2 2x9=18
3 3 3x3= 9
4 3 3x4=12
5 3 3x5=15
6 3 3x6=18
7 3 3x7=21
8 3 3x8=24
9 3 3x9=27
4 4 4x4=16
5 4 4x5=20
6 4 4x6=24
7 4 4x7=28
8 4 4x8=32
9 4 4x9=36
5 5 5x5=25
6 5 5x6=30
7 5 5x7=35
8 5 5x8=40
9 5 5x9=45
6 6 6x6=36
7 6 6x7=42
8 6 6x8=48
9 6 6x9=54
7 7 7x7=49
8 7 7x8=56
9 7 7x9=63
8 8 8x8=64
9 8 8x9=72
9 9 9x9=81
(45 行受影响)
-------------------------------------------------分割线-------------------------------------------
==================================================================================================
--1.
select * from t4 pivot (max(val) for col in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b
------------------------------------------------------------------------------------
row 9 8 7 6 5 4 3 2 1
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 NULL NULL NULL NULL NULL NULL NULL NULL 1x1= 1
2 NULL NULL NULL NULL NULL NULL NULL 2x2= 4 1x2= 2
3 NULL NULL NULL NULL NULL NULL 3x3= 9 2x3= 6 1x3= 3
4 NULL NULL NULL NULL NULL 4x4=16 3x4=12 2x4= 8 1x4= 4
5 NULL NULL NULL NULL 5x5=25 4x5=20 3x5=15 2x5=10 1x5= 5
6 NULL NULL NULL 6x6=36 5x6=30 4x6=24 3x6=18 2x6=12 1x6= 6
7 NULL NULL 7x7=49 6x7=42 5x7=35 4x7=28 3x7=21 2x7=14 1x7= 7
8 NULL 8x8=64 7x8=56 6x8=48 5x8=40 4x8=32 3x8=24 2x8=16 1x8= 8
9 9x9=81 8x9=72 7x9=63 6x9=54 5x9=45 4x9=36 3x9=27 2x9=18 1x9= 9
(9 行受影响)
-----------------------------------------------------------------------------------------
--2.
select * from t4 pivot (max(val) for col in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b
-------------------------------------------------------------------------------------------
row 1 2 3 4 5 6 7 8 9
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1x1= 1 NULL NULL NULL NULL NULL NULL NULL NULL
2 1x2= 2 2x2= 4 NULL NULL NULL NULL NULL NULL NULL
3 1x3= 3 2x3= 6 3x3= 9 NULL NULL NULL NULL NULL NULL
4 1x4= 4 2x4= 8 3x4=12 4x4=16 NULL NULL NULL NULL NULL
5 1x5= 5 2x5=10 3x5=15 4x5=20 5x5=25 NULL NULL NULL NULL
6 1x6= 6 2x6=12 3x6=18 4x6=24 5x6=30 6x6=36 NULL NULL NULL
7 1x7= 7 2x7=14 3x7=21 4x7=28 5x7=35 6x7=42 7x7=49 NULL NULL
8 1x8= 8 2x8=16 3x8=24 4x8=32 5x8=40 6x8=48 7x8=56 8x8=64 NULL
9 1x9= 9 2x9=18 3x9=27 4x9=36 5x9=45 6x9=54 7x9=63 8x9=72 9x9=81
(9 行受影响)
--------------------------------------------------------------------------------------------
--3.
select * from t4 pivot (max(val) for row in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b
---------------------------------------------------------------------------------------------
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 行受影响)
---------------------------------------------------------------------------------------------
--4.
select * from t4 pivot (max(val) for row in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b
---------------------------------------------------------------------------------------------
col 9 8 7 6 5 4 3 2 1
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1x9= 9 1x8= 8 1x7= 7 1x6= 6 1x5= 5 1x4= 4 1x3= 3 1x2= 2 1x1= 1
2 2x9=18 2x8=16 2x7=14 2x6=12 2x5=10 2x4= 8 2x3= 6 2x2= 4 NULL
3 3x9=27 3x8=24 3x7=21 3x6=18 3x5=15 3x4=12 3x3= 9 NULL NULL
4 4x9=36 4x8=32 4x7=28 4x6=24 4x5=20 4x4=16 NULL NULL NULL
5 5x9=45 5x8=40 5x7=35 5x6=30 5x5=25 NULL NULL NULL NULL
6 6x9=54 6x8=48 6x7=42 6x6=36 NULL NULL NULL NULL NULL
7 7x9=63 7x8=56 7x7=49 NULL NULL NULL NULL NULL NULL
8 8x9=72 8x8=64 NULL NULL NULL NULL NULL NULL NULL
9 9x9=81 NULL NULL NULL NULL NULL NULL NULL NULL
(9 行受影响)
----------------------------------------------------------------------------------------------------
select
'1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc
)b
select
'1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc
)b
select
'1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc
)b
select
'1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc
)b
select
'1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc
)b
select
'1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc
)b
select
'1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc
)b
select
'1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],
'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],
'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],
'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],
'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],
'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],
'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],
'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],
'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]
from
(
select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc
)b
/*
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。
问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)
--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b
--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')