当前位置： 移动技术网 > IT编程>开发语言>.net > SQL 横转竖 、竖专横 （转载）

SQL 横转竖 、竖专横 （转载）

2018年11月14日  | 移动技术网IT编程  | 我要评论

华夏地理杂志,hurtsobad,天天向上20130208

普通行列转换 
问题：假设有张学生成绩表(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 课程 
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

 

 

 

/*
普通行列转换


假设有张学生成绩表(tb)如下:
name subject result
张三 语文　　74
张三 数学　　83
张三 物理　　93
李四 语文　　74
李四 数学　　84
李四 物理　　94
*/

-------------------------------------------------------------------------
/*
想变成 
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/

createtable tb
(
   name    varchar(10) ,
   subject varchar(10) ,
   result  int
)

insertinto tb(name , subject , result) values('张三' , '语文' , 74)
insertinto tb(name , subject , result) values('张三' , '数学' , 83)
insertinto tb(name , subject , result) values('张三' , '物理' , 93)
insertinto tb(name , subject , result) values('李四' , '语文' , 74)
insertinto tb(name , subject , result) values('李四' , '数学' , 84)
insertinto tb(name , subject , result) values('李四' , '物理' , 94)
go

--静态sql,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when'语文'then result else0end) 语文,
  max(case subject when'数学'then result else0end) 数学,
  max(case subject when'物理'then result else0end) 物理
from tb
groupby name
/*
姓名         语文        数学        物理          
---------- ----------- ----------- ----------- 
李四         74          84          94
张三         74          83          93
*/

--动态sql,指subject不止语文、数学、物理这三门课程。
declare@sqlvarchar(8000)
set@sql='select name as '+'姓名'
select@sql=@sql+' , max(case subject when '''+ subject +''' then result else 0 end) ['+ subject +']'
from (selectdistinct subject from tb) as a
set@sql=@sql+' from tb group by name'
exec(@sql) 
/*
姓名         数学        物理        语文          
---------- ----------- ----------- ----------- 
李四         84          94          74
张三         83          93          74
*/

-------------------------------------------------------------------
/*加个平均分，总分
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/

--静态sql,指subject只有语文、数学、物理这三门课程。
select name 姓名,
  max(case subject when'语文'then result else0end) 语文,
  max(case subject when'数学'then result else0end) 数学,
  max(case subject when'物理'then result else0end) 物理,
  cast(avg(result*1.0) asdecimal(18,2)) 平均分,
  sum(result) 总分
from tb
groupby name
/*
姓名         语文        数学        物理        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         74          84          94          84.00                252
张三         74          83          93          83.33                250
*/

--动态sql,指subject不止语文、数学、物理这三门课程。
declare@sql1varchar(8000)
set@sql1='select name as '+'姓名'
select@sql1=@sql1+' , max(case subject when '''+ subject +''' then result else 0 end) ['+ subject +']'
from (selectdistinct subject from tb) as a
set@sql1=@sql1+' , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name'
exec(@sql1) 
/*
姓名         数学        物理        语文        平均分                总分          
---------- ----------- ----------- ----------- -------------------- ----------- 
李四         84          94          74          84.00                252
张三         83          93          74          83.33                250
*/

droptable tb    

---------------------------------------------------------
---------------------------------------------------------
/*
如果上述两表互相换一下：即

姓名 语文 数学 物理
张三 74　　83　　93
李四 74　　84　　94

想变成 
name       subject result      
---------- ------- ----------- 
李四         语文      74
李四         数学      84
李四         物理      94
张三         语文      74
张三         数学      83
张三         物理      93
*/

createtable tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)

insertinto tb1(姓名 , 语文 , 数学 , 物理) values('张三',74,83,93)
insertinto tb1(姓名 , 语文 , 数学 , 物理) values('李四',74,84,94)

select*from
(
  select 姓名 as name , subject ='语文' , result = 语文 from tb1 
  unionall
  select 姓名 as name , subject ='数学' , result = 数学 from tb1
  unionall
  select 姓名 as name , subject ='物理' , result = 物理 from tb1
) t
orderby name , case subject when'语文'then1when'数学'then2when'物理'then3when'总分'then4end

--------------------------------------------------------------------
/*加个平均分,总分
name       subject     result               
---------- -------    -------------------- 
李四         语文      74.00
李四         数学      84.00
李四         物理      94.00
李四         平均分    84.00
李四         总分      252.00
张三         语文      74.00
张三         数学      83.00
张三         物理      93.00
张三         平均分    83.33
张三         总分      250.00
*/

select*from
(
  select 姓名 as name , subject ='语文' , result = 语文 from tb1 
  unionall
  select 姓名 as name , subject ='数学' , result = 数学 from tb1
  unionall
  select 姓名 as name , subject ='物理' , result = 物理 from tb1
  unionall
  select 姓名 as name , subject ='平均分' , result =cast((语文 + 数学 + 物理)*1.0/3asdecimal(18,2)) from tb1
  unionall
  select 姓名 as name , subject ='总分' , result = 语文 + 数学 + 物理 from tb1
) t
orderby name , case subject when'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end

droptable tb1

 

转载来源:https://www.cnblogs.com/luowende2012/archive/2012/07/19/2599436.html

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网