接活跃气氛贴：9*9问题

Question

原帖：http://topic.csdn.net/u/20120526/13/11dbdbd6-c869-4078-8872-427b89ce6f6c.html--强大的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 行受影响)----------------------------------------------------------------------------------------------------

Answer 1

sql中PIVOT 用法详解 
PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。 PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。 PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。 在我们进行复杂的查询统计的时候，特别是销售统计、处理大量数据的时候，PIVOT的作用就显得非常突出。 案例分析：在开发一个收集客户资源的小型系统时，需要对客户的资源进行查询统计，本来想用原来的统计解决方案，但是哥们提出了使用Pivot函数，这个我还真没用过，所以就针对这个函数进行了一些学习。 每一个客户资源通过不同的渠道进来，需要公司成员对用户的信息进行处理，回访、邮件之类的，所以客户信息的状态需要修改，而且需要对每一种状态的客户信息进行统计。如果按照旧的逻辑，采用简单的Count语句去查询统计，SQL语句如下： select S.F_status,count(S.F_ID)as F_Count from c2c.dbo.T_Spread_customer as S 
group by S.F_status 
复制代码你得到的结果类似于： F_status F_Count
----------- -----------
NULL 4
1 1
4 2
5 1
6 5
7 1 如果是查询整个数据表的统计信息，或许这样做也不是很麻烦，只需要遍历你得到的表，取出数据，然后匹配到某一状态就可以。但是，如果根据客户信息不同来源进行统计，显然这样做，局限性很大，我们没有办法一次性得到各个来源的统计信息。而采用PIVOT，你会得到如下的结果： F_Num F_Source F_Total F_Normal F_Crm F_Wait F_InEffect F_Effect
----------- ------- ----------- ----------- ----------- ----------- ----------- -----------
1 First 3 0 2 0 0 1
2 Second 3 0 0 1 0 2
3 Third 3 0 0 0 1 2
4 Forth 1 1 0 0 0 0
5 Other 0 0 0 0 0 0 它把原来一列的数据，变成了Table的一行数据，而我们要展示给用户的也是这样一张表，所以利用此函数可以节省大量的逻辑代码。方便、快捷、高效。 具体的SQL语句如下： select * from 
( 
select S.F_status,count(S.F_ID)as F_Count from c2c.dbo.T_Spread_customer as S 
group by S.F_status 
)As T PIVOT(sum(T.F_Count) for T.F_Status in([1],[4],[5],[6],[7])) as C 
复制代码在这里，我对其显示进行一些加工和判定： 加工后的SQL语句 select F_PsnID,isnull([1],0)+isnull([4],0)+isnull([5],0)+isnull([6],0)+isnull([7],0) as 'F_Total',isnull([1],0) as 'F_Normal', 
isnull([4],0) as 'F_NormalCrm',isnull([5],0) as 'F_Wait',isnull([7],0) as 'F_InEffect',isnull([6],0) as 'F_Effect' 
from ( select S.F_PsnID,S.F_status,count(S.F_ID)as F_Count from c2c.dbo.T_Spread_customer as S 
where (1=1) 
group by S.F_PsnID,S.F_status )As T PIVOT(sum(T.F_Count) for T.F_Status in([1],[4],[5],[6],[7])) as C 
order by F_Total desc 
复制代码当然这只是个简单的小例子，你可以使用PIVOT，然后进行加工处理，它可以实现更为强大的功能。我在应用的时候用到了加入了临时表、分页等功能。 UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。但是在实际应用中，有些聚合之后的数据很难进行拆分。所以呢，UNPIVOT并非PIVOT的逆过程。 建议：如果你想了解的更加清楚，请参考：http://technet.microsoft.com/zh-cn/library/ms177410.aspx
注意：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。 有的SQL Server 2005初始安装时，默认的兼容级别为“80”，这时我们需要将兼容级别进行设置，不然，PIVOT不能正常的执行。我在使用PIVOT时就遇到这样的问题。 具体的修改方案如下： 修改兼容级别步骤
1、连接到相应的 SQL Server 数据库引擎实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。 2、展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。 3、右键单击数据库，再单击“属性”。 “数据库属性”对话框将打开。 4、在“选择页”窗格中，单击“选项”。 当前兼容级别显示在“兼容级别”列表框中。 5、若要更改兼容级别，请从列表中选择其他选项。 可用选项包括 SQL Server 2000 (80)、SQL Server 2005 (90) 或 SQL Server 2008 (100)。
具体的兼容级别之间的差异请参考：http://technet.microsoft.com/zh-cn/library/bb510680.aspx 如有不妥之处，请留言批评指正。上文来自:http://www.cnblogs.com/feiyublog/archive/2011/07/11/2103116.html

Answer 2

/*
标题：普通行列转换(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')

Answer 3

我也来整个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
)bselect 
'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
)bselect 
'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
)bselect 
'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

Answer 4

我也来整个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
)bselect 
'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
)bselect 
'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
)bselect 
'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