欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL 横转竖 、竖专横 (转载)

程序员文章站 2022-07-02 13:33:40
/*普通行列转换 假设有张学生成绩表(tb)如下:Name Subject Result张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94*/ /*想变成 姓名 语文 数学 物理 李四 74 84 94张三 74 83 93*/createtable t ......
普通行列转换 
问题:假设有张学生成绩表(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