一个SQL语句的写法

Question

表Aname   course   score
---------------------------------------------------------------
张三   语文     70
张三   语文     80
张三   数学     90
张三   数学     80
张三   英语     90
张三   英语     70
李四   语文     60
李四   语文     80
李四   数学     90
李四   数学     50
李四   英语     80
李四   英语     70怎样转换为
表B
name   语文   数学   英语
--------------------------------
张三   150    170    160
李四   140    140    150

Answer 1

--行列互转
--摘自中国风博客，引用请标明内容来源
--1、行换列
if object_id('Class') is not  null
    drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='    
   
    +quotename([Course],'''')+' then [Score] else 0 end)'
from 
    Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end) 
from 
    Class 
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select * 
from 
    Class 
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学         物理         英语         语文
------- ----------- ----------- ----------- -----------
李四     77          85          65          65
张三     87          90          82          78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select 
    [Student],
    [数学]=max(case when [Course]='数学' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英语]=max(case when [Course]='英语' then [Score] else 0 end),
    [语文]=max(case when [Course]='语文' then [Score] else 0 end),
    [总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from 
    Class 
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select  @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] 
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a 
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select 
    [Student],[数学],[物理],[英语],[语文],[总成绩] 
from 
    (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot 
    (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学         物理         英语         语文         总成绩
------- ----------- ----------- ----------- ----------- -----------
李四     77          85          65          65          292
张三     87          90          82          78          337(2 行受影响)
*/go--2、列转行
 
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四     数学     77
李四     物理     85
李四     英语     65
李四     语文     65
张三     数学     87
张三     物理     90
张三     英语     82
张三     语文     78
*/

Answer 2

--> 测试数据：[test]
go
if object_id('[test]') is not null 
drop table [test]
go
create table [test](
[name] varchar(4),
[course] varchar(4),
[score] int
)
go
insert [test]
select '张三','语文',70 union all
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','数学',80 union all
select '张三','英语',90 union all
select '张三','英语',70 union all
select '李四','语文',60 union all
select '李四','语文',80 union all
select '李四','数学',90 union all
select '李四','数学',50 union all
select '李四','英语',80 union all
select '李四','英语',70--2000动态转换
declare @str varchar(2000)
set @str=''
select 
     @str=@str+','+[course]+'=max(case when [course]='
     +''''+[course]+''''+' then [score] else 0 end)'
from 
     [test]
group by 
     [course]
exec('select [name] 姓名'+@str+' from [test] group by [name]')
/*
姓名 数学 英语 语文
李四 90 80 80
张三 90 90 80
*/
--2005以上版本select 
      *
from 
    [test]
pivot 
    (max([score]) for [course] in([数学],[英语],[语文]))b
/*
name 数学 英语 语文
李四 90 80 80
张三 90 90 80
*/

Answer 3

create table 表A
(name varchar(8), course varchar(6), score int)insert into 表A
select '张三', '语文', 70 union all
select '张三', '语文', 80 union all
select '张三', '数学', 90 union all
select '张三', '数学', 80 union all
select '张三', '英语', 90 union all
select '张三', '英语', 70 union all
select '李四', '语文', 60 union all
select '李四', '语文', 80 union all
select '李四', '数学', 90 union all
select '李四', '数学', 50 union all
select '李四', '英语', 80 union all
select '李四', '英语', 70
select name,[语文],[数学],[英语]
from 
(select name,course,sum(score) score 
from 表A group by name,course) t1
pivot(max(score) for course in([语文],[数学],[英语])) t2
order by name descname     语文          数学          英语
-------- ----------- ----------- -----------
张三       150         170         160
李四       140         140         150(2 row(s) affected)

Answer 4

if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (name nvarchar(4),course nvarchar(4),score int)
insert into [tb]
select '张三','语文',70 union all
select '张三','语文',80 union all
select '张三','数学',90 union all
select '张三','数学',80 union all
select '张三','英语',90 union all
select '张三','英语',70 union all
select '李四','语文',60 union all
select '李四','语文',80 union all
select '李四','数学',90 union all
select '李四','数学',50 union all
select '李四','英语',80 union all
select '李四','英语',70select * from [tb]select name ,SUM(case when course ='语文' then score else 0 end ) as '语文',
SUM(case when course ='数学' then score else 0 end ) as '数学',
SUM(case when course ='英语' then score else 0 end ) as '英语'
from TB
group by name 
/*
name 语文          数学          英语
---- ----------- ----------- -----------
李四   140         140         150
张三   150         170         160(2 行受影响)

Answer 5

   --楼上的解法我是没看懂
    --我的做法是将张三和李四的成绩分开
    --将他们的纪录分别查到两张表里
    --在对两张表里的数据利用group by句子和sum来求和
    --最后将两张表的查询结果插入一张表。
--OK
--寝室11点断网 没时间发SQL代码了。

Answer 6

我的做法比较复杂！   --创建一个数据库来存放原始数据
create database testcs
on
(name = 'testcs',filename = 'F:\fyj\sql serve 2005\database\testcs.mdf')
log on
(name = 'testcs_log',filename = 'F:\fyj\sql serve 2005\database\testcs_log.ldf')--创建一张表来存放你的原始数据
use testcs 
create table sc(cname varchar(20),course varchar(10),grade int)
alter table sc alter column cname insert into sc(cname,course,grade)
values('李四','英语','70')
--这里有12条记录 我就不列出来了，我全部都插入进去了--创建一张临时表 专门用来保存张三的信息
create table sctemp(cname varchar(20),course varchar(10),grade int)
insert into sctemp
select * from sc where cname = '张三'--创建一张临时表 用来保存李四的信息
create table sctemp1(cname varchar(20),course varchar(10),grade int)
insert into sctemp1
select * from sc where cname = '李四'--将SC表的信息清空
delete from sc--利用group by和sum函数对张三信息处理后插入sc表
insert into sc
select cname,course, sum(grade) as '总成绩' from sctemp group by course,course,cname--同理将李四的信息处理插入SC表
insert into sc
select cname,course, sum(grade) as '总成绩' from sctemp1 group by course,course,cname--最后将sctemp表 和 sctemp1表删除
drop table sctemp
drop table sctemp1
--这个不理解的话 就照下面的子查询运行一遍就知道了
select grade1 '数学' ,grade2 '英语',grade3 '语文'
from
(select * from 
(select a.cname cname1,a.course course1,a.grade grade1,b.cname cname2,b.course course2,b.grade grade2,c.cname cname3,c.course course3,c.grade grade3 
from sc as a,sc as b, sc as c where a.cname = b.cname and b.cname = c.cname)temp
where temp.course1<>temp.course2 and temp.course2<>temp.course3 and temp.course1<>temp.course3)temp1
where course1 = '数学' and course2 = '英语'
--将以上的三个子查询分别运行一次你就明白了
temp (select a.cname cname1,a.course course1,a.grade grade1,b.cname cname2,b.course course2,b.grade grade2,c.cname cname3,c.course course3,c.grade grade3 
from sc as a,sc as b, sc as c where a.cname = b.cname and b.cname = c.cname)temp1 select * from temp 
where temp.course1<>temp.course2 and temp.course2<>temp.course3 and temp.course1<>temp.course3)select grade1 '数学' ,grade2 '英语',grade3 '语文' from temp1 
where course1 = '数学' and course2 = '英语'

Answer 7

我发现我没理解楼主的意思！ 楼主要求行和列互换，所以我们错了！ 下面是我重新更正后的sql代码！ 

Answer 8

标题：普通行列转换(version 2.0)说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(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 tbgroup 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 aset @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 tbgroup 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 aset @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 姓名) nwhere 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 姓名) nwhere 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 allselect 姓名 , 课程 = '数学' , 分数 = 数学 from tbunion allselect 姓名 , 课程 = '物理' , 分数 = 物理 from tb) torder 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 ascexec(@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 allselect 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tbunion allselect 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tbunion allselect 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tbunion allselect 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb) torder by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb--> 生成测试数据: #DB_infoif object_id('tempdb.dbo.#DB_info') is not null drop table #DB_infocreate table #DB_info (sid int,name nvarchar(4),sex nvarchar(2))insert into #DB_infoselect 1,'李明','男' union allselect 2,'王军','男' union allselect 3,'李敏','女'--> 生成测试数据: #db_scoresif object_id('tempdb.dbo.#db_scores') is not null drop table #db_scorescreate table #db_scores (sid int,type nvarchar(4),scores int)insert into #db_scoresselect 1,'语文',80 union allselect 1,'数学',90 union allselect 2,'语文',85 union allselect 2,'数学',90 union allselect 3,'语文',75 union allselect 3,'数学',85declare @sql nvarchar(4000)set @sql='select a.sid,a.name,a.sex'select @sql=@sql+',max(case when b.type='''+type+''' then b.scores else 0 end) ['+type+']'from (select distinct type from #db_scores) texec (@sql+' from #DB_info a left outer join #db_scores b on a.sid=b.sid group by a.sid,a.name,a.sex')/*sid name sex 数学 语文----------- ---- ---- ----------- -----------1 李明 男 90 802 王军 男 90 853 李敏 女 85 75(3 行受影响)*/这个是网上的例子，希望对你有帮助，我以前用到的时候找的

Answer 9

思路就是分两步走：第一步先SUM（score），第二步行转列。MSSQL2005及以上版本：
create table t1
(
name varchar(10),
course varchar(10),
score int
)
insert into t1
select '张三', '语文', 70 union all
select '张三', '语文', 80 union all
select '张三', '数学', 90 union all
select '张三', '数学', 80 union all
select '张三', '英语', 90 union all
select '张三', '英语', 70 union all
select '李四', '语文', 60 union all
select '李四', '语文', 80 union all
select '李四', '数学', 90 union all
select '李四', '数学', 50 union all
select '李四', '英语', 80 union all
select '李四', '英语', 70
select * from t1;with aaa as
(
select name,course,SUM(score) as score
from t1
group by name,course
)
select name,
MAX(case when course='语文' then score end) as '语文',
MAX(case when course='数学' then score end) as '数学',
MAX(case when course='英语' then score end) as '英语'
from aaa
group by name
order by name desc------------------------
name 语文 数学 英语
张三 150 170 160
李四 140 140 150

Answer 10

 <font face="Arial" size=2>
<p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e14'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'tblGrp_Users'.</font>
<p>
<font face="Arial" size=2>/arabic-site/news/viewMobileDetails.asp</font><font face="Arial" size=2>, line 22</font> 这事返回的所有的求指导。。求教育。。
我用的语句是
 and (select top 1 name from (select top __i__ name,id from <dbname>..syscolumns where id=object_id('<tablename>') order by name) t order by name desc)=0
 and (select col_name(object_id('<tablename>'),__i__))=0
积分不够了、、、、

Answer 11

row to column, old topic

Answer 12

我没分了。想占点便宜啊。大侠们像这样的SQL语句格式有木有实例啊。给一个嘛。非常感谢了。我QQ429592913格式：艺术0-1000  艺术1000以上   婚5999以下   6000以上   10000以上   HT-3999以下
个   金额   个 金额   个 金额 个   金额  个 金额   个    金额有实例的可以给我一个真的非常谢谢了

Answer 13

if object_id('tempdb..#tb') is not null
drop table #tb
create table #tb
(
 tname    varchar(20),
 course   varchar(20),
 score    int
)
insert into #tb(tname,course,score)
select '张三','语文',70
union all
select '张三','语文',80
union all
select '张三','数学',90
union all
select '张三','数学',80
union all
select '张三','英语',90
union all
select '张三','英语',80select tname ,sum(case when course='语文' then score else 0 end) '语文',
sum(case when course='数学' then score else 0 end) '数学',
sum(case when course='英语' then score else 0 end) '英语'
from #tb
group by tname--(1 行受影响)select * from #tb

Answer 14

--表B
--name 语文 数学 英语
----------------------------------
--张三 150 170 160
--李四 140 140 150if object_id('A') is not null 
drop table A
Go
Create table A([name] nvarchar(2),[course] nvarchar(2),[score] smallint)
Insert into A
Select N'张三',N'语文',70
Union all Select N'张三',N'语文',80
Union all Select N'张三',N'数学',90
Union all Select N'张三',N'数学',80
Union all Select N'张三',N'英语',90
Union all Select N'张三',N'英语',70
Union all Select N'李四',N'语文',60
Union all Select N'李四',N'语文',80
Union all Select N'李四',N'数学',90
Union all Select N'李四',N'数学',50
Union all Select N'李四',N'英语',80
Union all Select N'李四',N'英语',70
;WITH cte AS (
select name,course,SUM(score) AS score from A
GROUP BY name,course
)
SELECT name,语文,数学,英语 FROM cte
PIVOT (
max(score) FOR course IN(语文,数学,英语)
) AS p
Go