日期:2014-05-16  浏览次数:20457 次

想把查询效果行变列做成视图
经典的行变列例子
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
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 姓名) n
where m.姓名 = n.姓名')


如果把查询出来的结果做成视图(因为工程是用codesmith做的,不让用ado)
------解决方案--------------------
视图里好像不能declare和exec,做个存储过程吧,返回结果集
------解决方案--------------------
;with obj
as(
select *
from tb
pivot(
sum([分数])
for [课程]
in ([语文],[数学],[物理])
)p)
select *,[平均分]=([语文]+[数学]+[物理])/3,[总分]=([语文]+[数学]+[物理])
from obj

------解决方案--------------------
在IN里面增加,平均分和总分也一样
------解决方案--------------------
别把思路局限在视图啊
--create table tb(姓名 Nvarchar(10) , 课程 Nvarchar(10) , 分数 int)
--insert into tb values(N'张三' , N'语文' , 74)
--insert into tb values(N'张三' , N'数学' , 83)
--insert into tb values(N'张三' , N'物理' , 93)
--insert into tb values(N'李四' , N'语文' , 74)
--insert into tb values(N'李四' , N'数学' , 84)
--insert into tb values(N'李四' , N'物理' , 94)
--go

CREATE PROC test 
AS 
declare @sql Nvarchar(4000)
select @sql = isnull(@sql + N',' , N'') + 课程 from tb group by 课程
EXEC  (N'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 姓名) n
where m.姓名 = n.姓名')


EXEC test

/*
姓名         数学          物理          语文          平均分                                     总分
---------- ----------- ----------- ----------- --------------------------------------- --