我在MSSQL中写了以下的SQL语句
declare @sql varchar(8000)
set @sql = 'select zjid,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go这样得到的结果是name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60 我还想得到每一行的平均值是如何写SQL语句,请大家帮帮忙以下是建表语句
--交叉表实例-- ======================================================建表:在查询分析器里运行:CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY]GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Go
declare @sql varchar(8000)
set @sql = 'select zjid,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go这样得到的结果是name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60 我还想得到每一行的平均值是如何写SQL语句,请大家帮帮忙以下是建表语句
--交叉表实例-- ======================================================建表:在查询分析器里运行:CREATE TABLE [Test] ( [id] [int] IDENTITY (1, 1) NOT NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [Source] [numeric](18, 0) NULL ) ON [PRIMARY]GOINSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)Go
declare @sql varchar(8000)
set @sql = 'select zjid,'
select @sql = @sql + 'sum(case subject when '''+subject+'''
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go是怎么会得出这样的结果的???name 数学 英语 语文
李四 70 0 80
王五 75 80 57
张三 0 100 60如果要求平均数的话就利用子查询好了如:select a.name,a.数学,a.英语,a.语文,(a.数学+a.英语+a.语文)/3.0 平均 from
(select ......)a
declare @sql2 varchar(8000)
declare @sql3 varchar(8000)
set @sql1 = 'select a.name,'
set @sql2 = '('
set @sql3 = 'select name,'
select
@sql1 = @sql1 + 'a.'+subject+',' ,
@sql2 = @sql2 + 'a.'+subject+'+',
@sql3 = @sql3 + 'sum(case subject when '''+subject+''' then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as aset @sql1 = @sql1 +
left(@sql2 , len(@sql2)-1) + ')/' + (select cast(count (distinct subject) as varchar) from test) +' as ''平均'' from(' +
left(@sql3 , len(@sql3)-1) + 'from test group by name ) a'
exec(@sql1)
GO
结果如下: name 数学 英语 语文 平均
---------------------------------------
李四 70 0 80 50.000000
王五 75 80 57 70.666666
张三 0 100 60 53.333333