SQL 行转列，列转行 SQL2000/2005分别写法

--行转列(SQL2005)
--建表
create table test(编号 int,姓名 varchar(20),季度 int,销售额 int)
insert into test values(1,'simon',1,1000)
insert into test values(1,'simon',2,2000)
insert into test values(1,'simon',3,3000)
insert into test values(1,'simon',4,4000)
insert into test values(2,'meme',1,5000)
insert into test values(2,'meme',2,6000)
insert into test values(2,'meme',3,7000)
insert into test values(2,'meme',4,8000)
--执行普通查询
select * from test
--执行转换查询
select 编号,姓名,[1] as "一季度",[2] as "二季度",[3] as "三季度",[4] as "四季度",[5] as "随便1" from test pivot(sum(销售额) for 季度 in([1],[2],[3],[4],[5]))as pvt
--行转列(SQL2000)
/*
普通行列转换
(爱新觉罗.毓华 2007-11-18于海南三亚)

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

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

create table tb
(
   Name    varchar(10) ,
   Subject varchar(10) ,
   Result  int
)

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74)
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83)
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93)
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74)
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84)
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94)
go

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

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

--动态SQL,指subject不止语文、数学、物理这三门课程。
declare @sql1 varchar(8000)
set @sql1 = 'select Name as ' + '姓名'
select @sql1 = @sql1 + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
from (select distinct 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
*/

drop table tb   

 

--列转行(SQL2005)
--建表
create table test2(编号 int,姓名 varchar(20), 一季度 int, 二季度 int, 三季度 int, 四季度 int)
insert into test2 values(1,'simon',1000,2000,4000,5000)
insert into test2 values(2,'meme',3000,3500,4200,5500)
--执行普通查询
select * from test2
--执行转换查询
select 编号,姓名,季度,销售额 from test2 unpivot(销售额 for 季度 in(一季度,二季度,三季度,四季度)) as unpvt

--列转行(SQL2000)
create table tb1
(
   姓名 varchar(10) ,
   语文 int ,
   数学 int ,
   物理 int
)

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

select * from
(
  select 姓名 as Name , Subject = '语文' , Result = 语文 from tb1  
  union all
  select 姓名 as Name , Subject = '数学' , Result = 数学 from tb1
  union all
  select 姓名 as Name , Subject = '物理' , Result = 物理 from tb1
) t
order by name , case Subject when '语文' then 1 when '数学' then 2 when '物理' then 3 when '总分' then 4 end