旋转列

iwillbeback_good 2010-04-24 11:43:39
如题
...全文
64 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
htl258_Tony 2010-04-24
  • 打赏
  • 举报
 回复 
--行列互转

/******************************************************************************************************************************************************

以学生成绩为例子，比较形象易懂



整理人：中国风(Roy)



日期:2008.06.06

******************************************************************************************************************************************************/



--1、行互列

--> --> (Roy)生成測試數據



if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)

Insert Class

select N'张三',N'语文',78 union all

select N'张三',N'数学',87 union all

select N'张三',N'英语',82 union all

select N'张三',N'物理',90 union all

select N'李四',N'语文',65 union all

select N'李四',N'数学',77 union all

select N'李四',N'英语',65 union all

select N'李四',N'物理',85 

Go

--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+' from Class group by [Student]')





生成静态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 

from 

    Class 

group by [Student]



GO

动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]

exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')



生成静态:

select * 

from 

    Class 

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student 数学          物理          英语          语文

------- ----------- ----------- ----------- -----------

李四      77          85          65          65

张三      87          90          82          78



(2 行受影响)

*/



------------------------------------------------------------------------------------------

go

--加上总成绩(学科平均分)



--2000方法:

动态:



declare @s nvarchar(4000)

set @s=''

Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'

from Class group by[Course]

exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))



生成动态:



select 

    [Student],

    [数学]=max(case when [Course]='数学' then [Score] else 0 end),

    [物理]=max(case when [Course]='物理' then [Score] else 0 end),

    [英语]=max(case when [Course]='英语' then [Score] else 0 end),

    [语文]=max(case when [Course]='语文' then [Score] else 0 end),

    [总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))

from 

    Class 

group by [Student]



go



--2005方法:



动态:



declare @s nvarchar(4000)

Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号

exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 

pivot (max([Score]) for [Course] in('+@s+'))b ')



生成静态:



select 

    [Student],[数学],[物理],[英语],[语文],[总成绩] 

from 

    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])

pivot 

    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 



生成格式:



/*

Student 数学          物理          英语          语文          总成绩

------- ----------- ----------- ----------- ----------- -----------

李四      77          85          65          65          292

张三      87          90          82          78          337



(2 行受影响)

*/



go



--2、列转行

--> --> (Roy)生成測試數據



if not object_id('Class') is null

    drop table Class

Go

Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)

Insert Class

select N'李四',77,85,65,65 union all

select N'张三',87,90,82,78

Go



--2000:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all

+',[Score]='+quotename(Name)+' from Class'

from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列

order by Colid

exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序



生成静态:

select * 

from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 

select [Student],[Course]='物理',[Score]=[物理] from Class union all 

select [Student],[Course]='英语',[Score]=[英语] from Class union all 

select [Student],[Course]='语文',[Score]=[语文] from Class)t 

order by [Student],[Course]



go

--2005:



动态:



declare @s nvarchar(4000)

select @s=isnull(@s+',','')+quotename(Name)

from syscolumns where ID=object_id('Class') and Name not in('Student') 

order by Colid

exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')



go

select 

    Student,[Course],[Score] 

from 

    Class 

unpivot 

    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b



生成格式:

/*

Student Course Score

------- ------- -----------

李四      数学      77

李四      物理      85

李四      英语      65

李四      语文      65

张三      数学      87

张三      物理      90

张三      英语      82

张三      语文      78



(8 行受影响)

*/







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/03/01/3947993.aspx
--小F-- 2010-04-24
  • 打赏
  • 举报
 回复
楼主去查询pivot和 unpivot 函数
--小F-- 2010-04-24
  • 打赏
  • 举报
 回复 
*

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



---------------------------------



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

---- ---- ---- ---- ------ ----

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理,

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

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 + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb    



------------------

------------------



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

---- ---- ----

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

--------------

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



--------------------

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

---- ------ ------

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

------------------

*/



select * from

(

 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

 union all

 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

 union all

 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
hzvcan 2010-04-24
  • 打赏
  • 举报
 回复 
create table test(id int,name varchar(20),quarter int,profile int) 

insert into test values(1,'a',1,1000)

insert into test values(1,'a',2,2000)

insert into test values(1,'a',3,4000)

insert into test values(1,'a',4,5000)

insert into test values(2,'b',1,3000)

insert into test values(2,'b',2,3500)

insert into test values(2,'b',3,4200)

insert into test values(2,'b',4,5500)

select top 1 * from test



select id,name,

[1] as '一季度',

[2] as '二季度',

[3] as '三季度',

[4] as '四季度'

from

test

pivot

(

sum(profile)

for quarter in

([1],[2],[3],[4])

)

as pvt

/*

<pivoted_table> ::=

        table_source PIVOT ( aggregate_function ( value_column ) 

        FOR pivot_column 

        IN ( <column_list> )  table_alias



<pivot_clause> ::=

        ( aggregate_function ( value_column ) 

        FOR pivot_column 

        IN ( <column_list> ) 



/*pivot_column 和 value_column 是 PIVOT 运算符使用的组合列。PIVOT 遵循以下过程获得输出结果集： 



对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。 



输出行中的分组列获得 input_table 中该组的对应列值。 



通过执行以下操作，为每个输出行生成列列表中的列的值： 



针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。



对于 column_list 中的每个输出列，选择满足以下条件的子组：



pivot_column = CONVERT(<data type of pivot_column>, 'output_column')





针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。

如果该子组为空，SQL Server 将为该 output_column 生成空值。如果聚合函数是 COUNT，且子组为空，则返回零 (0)。*/
YOLOv8旋转目标检测实战：训练自己的数据集
旋转目标检测是计算机视觉领域的一个高级任务，它在传统目标检测的基础上进一步发展。传统目标检测技术主要关注于识别和定位图像中的物体，通常以水平边界框(HBB)来标识目标物体的位置。而旋转目标检测则旨在识别并...
旋转矩阵列向量的物理含义
旋转矩阵列向量的物理含义
关于三维空间绕坐标轴的旋转变换的左右手坐标系区分详解
关于三维空间绕坐标轴的旋转变换的左右手坐标系区分详解 左右手区别： 右手坐标系进行变换时将T与需要变换的点或向量A（列向量）相乘，即TA。 左手坐标系进行变换时将T与需要变换的点或向量A（行向量）相乘，即AT。 仅仅是矩阵乘法的顺序不一样，结果是一样的，但是两者不要混用 绕X轴的旋转 在三维场景中，当一个点P(x,y,z)绕x轴旋转θ角得到点P’(x’,y’,z’)。由于是绕x轴进行的旋转...
旋转向量与欧拉角 罗德里格斯公式（Rodrigues's Formula）
旋转向量 旋转矩阵表达方式 旋转矩阵描述旋转，变换矩阵描述一个6自由度的三维刚体运动。但存在如下缺点： SO(3)的旋转矩阵有9个量，但一次旋转只有3个自由度。因此这种表达方式是冗余的。同理，变换矩阵16个量表达了6个自由度的变换，也不够紧凑。 旋转矩阵自身带有约束：它必须是个正交矩阵，且行列式为1。变换矩阵也是如此。当想要估计或优化一个旋转矩阵/变换矩阵时，这些约束会使得求解变得困难。 外...
Matlab中rot90函数用法
目录 rot90 语法 说明 示例 旋转列向量 旋转多维数组 输入参数 提示 rot90 将数组旋转 90 度 语法 B = rot90(A) B = rot90(A,k) 说明 B = rot90(A)将数组A逆时针旋转 90 度。对于多维数组，rot90在由第一个和第二个维度构成的平面中旋转。 B = rot90(A,k)将数组A按逆时针方向旋转k*90度，其中k是一个整数。 示例 旋转列向量 创建一个包含序列元素的列向量。 A ...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章