高分求行转换成列的SQL

Question

table1
课程名     小红       小兰
A        1200      300
B        800       200
C        400       100table2
姓名      A        B         C
小红    1200      800       400
小兰     300      200       100用SQL如何将table1转化成table2的形式呢？
谢谢

Answer 1

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

Answer 2

可能楼主要的不是存储过程了。我以前也提过风样的问题。只能用存储过程了。这个应该个个都会的。其实我也希望一句话就可以搞定的SQL。（非调用存储过程）

Answer 3

--测试数据   
  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   基础表   

Answer 4

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 ,ageselect loction = (case 
                    when work = (select top 1 work from # where loction=a.loction) then a.loction
                    else ''    end) ,
work,[5],[10],[15],age
from # adrop 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 行受影响)*/

Answer 5

--这是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 )
goselect 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
) mdrop 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 行受影响)
*/

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

--> 测试数据: [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

Answer 10

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

Answer 11

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
*/

Answer 12

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
*/

Answer 13

if object_id('table1') is not null
 drop table table1
gocreate 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
*/

Answer 14

看过了，值得学习，请问如下：
table1 
课程名    小红      小兰 
A        1200      300 
B        800      200 
C        400      100 table2 
姓名      A        B        C 
小红    1200      800      400 
小兰    300      200      100 要是用SQL如何将table2转化成table1的形式呢？  各位大侠，谢谢了……