高分求行转换成列的SQL

table1
课程名     小红       小兰
A        1200      300
B        800       200
C        400       100

table2
姓名      A        B         C
小红    1200      800       400
小兰     300      200       100

用SQL如何将table1转化成table2的形式呢？
谢谢

30 个解决方案

#1

要求行变列，列变行

#2

问题：假设有张学生成绩表(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')

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

#3

学习了。

#4

可能楼主要的不是存储过程了。我以前也提过风样的问题。只能用存储过程了。这个应该个个都会的。

其实我也希望一句话就可以搞定的SQL。（非调用存储过程）

#5

--测试数据   
  create   table   基础表(姓名   nvarchar(10),课程名   nvarchar(20),成绩   decimal(10,1))   
  insert   基础表   select   '王家喜','计算机基础',69.0   
  union     all       select   '王家喜','*理论',74.0   
  union     all       select   '王家喜','英语（上）',86.0   
  union     all       select   '王家喜','普通逻辑学',91.0   
  union     all       select   '施春林','立法学教程',60.0   
  union     all       select   '施春林','经管原理'     ,73.0   
  union     all       select   '施春林','英语（上）',73.0   
  union     all       select   '施春林','普通逻辑学',90.0   
  go   
  
select * from 基础表
  --查询   
  declare   @s   varchar(8000),@i   varchar(10)   
  select   top   1   @s='',@i=count(*)   
  from   基础表   group   by   姓名   order   by   count(*)   desc   
  while   @i>0   
  select   @s=',[课程'+@i+']=max(case   when   id='+@i   
  +'   then   课程名   else   ''''   end),[成绩'+@i   
  +']=max(case   when   id='+@i   
  +'   then   成绩   end)'+@s   
  ,@i=@i-1   
  exec('   
  select   姓名,课程名,成绩,id=0   into   #t   from   基础表   order   by   姓名   
  declare   @i   int,@姓名   varchar(10)   
  update   #t   set   @i=case   when   @姓名=姓名   then   @i+1   else   1   end,id=@i,@姓名=姓名   
  select   姓名'+@s+'   from   #t   group   by   姓名')   
  go   
    
  --删除测试   
  drop   table   基础表   

#6

create table tb(LocTion varchar(10),work varchar(10),money int,time int,age int)
insert into tb values('广州', '程序员', 1000 , 5  , 22 )
insert into tb values('广州', '教师'  , 1700 , 10 , 22 )
insert into tb values('广州', '警察'  , 1300 , 15 , 22 )
insert into tb values('广州', '警察'  , 800  , 5  , 22 )
insert into tb values('上海', '程序员', 1600 , 5  , 21 )
insert into tb values('上海', '司机'  , 1200 , 15 , 21 )
insert into tb values('北京', '程序员', 1400 , 5  , 29 )
go


select loction , work,
  sum(case time when 5 then money else 0 end) [5],
  sum(case time when 10 then money else 0 end) [10],
  sum(case time when 15 then money else 0 end) [15],
  age
into #
from tb
group by loction , work ,age

select loction = (case 
                    when work = (select top 1 work from # where loction=a.loction) then a.loction
                    else ''    end) ,
work,[5],[10],[15],age
from # a

drop table tb,#

/*
loction    work       5           10          15          age         
---------- ---------- ----------- ----------- ----------- ----------- 
北京         程序员        1400        0           0           29
广州         程序员        1000        0           0           22
             教师         0           1700        0           22
             警察         800         0           1300        22
上海         程序员        1600        0           0           21
             司机         0           0           1200        21

（所影响的行数为 6 行）

(6 行受影响)

*/

#7

--这是2005使用row_number的处理办法,如果是2000,要将数据插入一临时表来解决.具体方法见我上面贴出地址.
create table tb(LocTion varchar(10),work varchar(10),money int,time int,age int)
insert into tb values('广州', '程序员', 1000 , 5  , 22 )
insert into tb values('广州', '教师'  , 1700 , 10 , 22 )
insert into tb values('广州', '警察'  , 1300 , 15 , 22 )
insert into tb values('广州', '警察'  , 800  , 5  , 22 )
insert into tb values('上海', '程序员', 1600 , 5  , 21 )
insert into tb values('上海', '司机'  , 1200 , 15 , 21 )
insert into tb values('北京', '程序员', 1400 , 5  , 29 )
go

select loction = case when px = (select min(px) from
  (
    select * , px = row_number() over(order by loction , work , [5] , [10] , [15] , age) from
    (
      select loction , work,
        sum(case time when 5 then money else 0 end) [5],
        sum(case time when 10 then money else 0 end) [10],
        sum(case time when 15 then money else 0 end) [15],
        age
      from tb
      group by loction , work ,age
    ) t
  ) n
  where n.loction = m.loction) then loction else '' end,
  work , [5],[10],[15],age
from 
(
  select * , px = row_number() over(order by loction , work , [5] , [10] , [15] , age) from
  (
    select loction , work,
      sum(case time when 5 then money else 0 end) [5],
      sum(case time when 10 then money else 0 end) [10],
      sum(case time when 15 then money else 0 end) [15],
      age
    from tb
    group by loction , work ,age
  ) t
) m

drop table tb

/*
loction    work       5           10          15          age
---------- ---------- ----------- ----------- ----------- -----------
北京       程序员     1400        0           0           29
广州       程序员     1000        0           0           22
           教师       0           1700        0           22
           警察       800         0           1300        22
上海       程序员     1600        0           0           21
           司机       0           0           1200        21

(6 行受影响)
*/

#8

参考：http://topic.csdn.net/u/20090428/21/105395c0-cf52-4b99-ae8a-5db6bd0bfb71.html

#9

用case语句就行了

#10

学习

#11

引用 2 楼 zzxap 的回复:

SQL code
问题：假设有张学生成绩表(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 int…

ding

#12

参考
参考

#13

学习是一定要的
>>>>>>>>>>------------------------------------------------------------------------------------------<<<<<<<<<<

#14

select
  name='小红',
  A=max(case course when 'A' then name1 else 0 end),
  B=max(case course when 'B' then name1 else 0 end),
  C=max(case course when 'C' then name1 else 0 end)
from 
  tb
union all
select
  name='小兰',
  A=max(case course when 'A' then name2 else 0 end),
  B=max(case course when 'B' then name2 else 0 end),
  C=max(case course when 'C' then name2 else 0 end)
from 
  tb

#15

其实就case和pivot

#16

--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (课程名 varchar(1),小红 int,小兰 int)
insert into [table1]
select 'A',1200,300 union all
select 'B',800,200 union all
select 'C',400,100

--数据处理
declare @f1 varchar(8000),@f2 varchar(8000),@f3 varchar(8000)
select @f1='',@f2='',@f3=''
select @f1=@f1+',['+课程名+']='''+ltrim(小红)+''''
    ,@f2=@f2+','''+ltrim(小兰)+''''
from table1
exec('select 姓名=''小红'''+@f1
    +' union all select ''小兰'''+@f2)
go

--结果：
姓名   A    B    C
---- ---- ---- ----
小红   1200 800  400
小兰   300  200  100

#17

select
  name='小红',
  A=max(case course when 'A' then 小红 else 0 end),B=max(case course when 'B' then 小红 else 0 end),C=max(case course when 'C' then 小红 else 0 end)
from  tb
union all
select
  name='小兰',
  A=max(case course when 'A' then 小兰2 else 0 end),B=max(case course when 'B' then 小兰 else 0 end), C=max(case course when 'C' then 小兰 else 0 end)
from  tb

#18

pt1314917厉害

#19

create table tab1 (程名 char(4),小红 int,小兰 int)
insert into tab1 select
'A',1200,300 union all select 
'B',800 ,200 union all select 
'C',400 ,100 
select * from tab1
declare @f1 varchar(8000),@f2 varchar(8000)
select @f1='',@f2=''
select @f1=@f1+',['+程名+']='''+cast([小红] as varchar(10))+''''
	,@f2=@f2+','''+cast([小兰] as varchar(10))+''''
from tab1
exec('select 姓名=''小红'''+@f1
	+' union all select ''小兰'''+@f2)
go


/*

姓名   A    B    C    
---- ---- ---- ---- 
小红   1200 800  400
小兰   300  200  100
*/

#20

慢了啊

#21

引用 18 楼 zzxap 的回复:

pt1314917厉害

是邹老大牛

#22

table1 
课程名    小红      小兰 
A        1200      300 
B        800      200 
C        400      100 

table2 
姓名      A        B        C 
小红    1200      800      400 
小兰    300      200      100 
create table tab1 (姓名 char(4),小红 int,小兰 int)
insert into tab1 select
'A',1200,300 union all select 
'B',800 ,200 union all select 
'C',400 ,100 
select * from tab1
declare @f1 varchar(8000),@f2 varchar(8000)
select @f1='',@f2=''
select @f1=@f1+',['+姓名+']='''+cast([小红] as varchar(10))+''''
    ,@f2=@f2+','''+cast([小兰] as varchar(10))+''''
from tab1
exec('select 姓名=''小红'''+@f1
    +' union all select ''小兰'''+@f2)
go


/*

姓名   A    B    C    
---- ---- ---- ---- 
小红   1200 800  400
小兰   300  200  100
*/

#23

if object_id('table1') is not null
 drop table table1
go

create table table1(课程名 varchar(10),小红 int,小兰 int)
insert into table1 
select 'A',        1200,     300  union all
select 'B',        800,      200  union all
select 'C',        400,      100  

select 姓名,
       sum(case when 课程名='A' then 分数 else 0 end) [A],
       sum(case when 课程名='B' then 分数 else 0 end) [B],
       sum(case when 课程名='C' then 分数 else 0 end) [C]
from
(
	select 课程名,
		   姓名='小红',  
		   分数=小红
	from table1
	union all
	select 课程名,
		   姓名='小兰',  
		   分数=小兰
	from table1
)T
group by 姓名
/*
结果
姓名     A      B    C
小红	1200	800	400
小兰	300	    200	100
*/

#24

用过通用交叉表存储过程看看。

#25

看过了，值得学习，请问如下：
table1 
课程名    小红      小兰 
A        1200      300 
B        800      200 
C        400      100 

table2 
姓名      A        B        C 
小红    1200      800      400 
小兰    300      200      100 

要是用SQL如何将table2转化成table1的形式呢？  各位大侠，谢谢了……

#26

学习，顶起

#27

只是追求一个结果没什么意义的，能够适应于大量数据，动态显示才是有意义，上面的很多回复都没动态显示。
要动态还是需要用服务器后台代码来控制：

#28

回答的都好经典

#29

说的好

#30

其实我也是想知道动态的怎么写
同样感谢各位热心指教