纵表转横表(SQL)

/* 普通行列转换 (爱新觉罗.毓华 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      --------------------------------------------------------- --------------------------------------------------------- /* 如果上述两表互相换一下：即   姓名 语文 数学 物理 张三 74　　83　　93 李四 74　　84　　94   想变成  Name        Subject Result       ---------- ------- -----------  李四         语文      74 李四         数学      84 李四         物理      94 张三         语文      74 张三         数学      83 张三         物理      93 */   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   -------------------------------------------------------------------- /*加个平均分,总分 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     union  all    select  姓名  as  Name  , Subject =  ‘数学‘  , Result = 数学  from  tb1    union  all    select  姓名  as  Name  , Subject =  ‘物理‘  , Result = 物理  from  tb1    union  all    select  姓名  as  Name  , Subject =  ‘平均分‘  , Result =  cast ((语文 + 数学 + 物理)*1.0/3  as  decimal (18,2))  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  when  ‘总分‘  then  5  end   drop  table  tb1

纵表转横表(SQL)

标签：