--测试环境
create table t (Grade varchar(5),Mt varchar(2))
insert into T select 'c1f','a1'
union all select 'c2f','a1'
union all select 'c3f','a1'
union all select 'c4f','b1'
union all select 'c1f','a2'
union all select 'c2f','a2'
union all select 'b1f','a2'
--动态SQL
declare @r varchar(8000)
set @r=''
select @r=@r+', ['+Mt+']= isnull(case when Mt='''+Mt+''' then Grade end,'''') '
from T
group by MT
set @r='select '+stuff(@r,1,1,'')+' from T'
exec(@r)
--结果
a1 a2 b1
----- ----- -----
c1f
c2f
c3f
c4f
c1f
c2f
b1f
--删除测试环境
drop table T
create table t (Grade varchar(5),Mt varchar(2))
insert into T select 'c1f','a1'
union all select 'c2f','a1'
union all select 'c3f','a1'
union all select 'c4f','b1'
union all select 'c1f','a2'
union all select 'c2f','a2'
union all select 'b1f','a2'
--动态SQL
declare @r varchar(8000)
set @r=''
select @r=@r+', ['+Mt+']= isnull(case when Mt='''+Mt+''' then Grade end,'''') '
from T
group by MT
set @r='select '+stuff(@r,1,1,'')+' from T'
exec(@r)
--结果
a1 a2 b1
----- ----- -----
c1f
c2f
c3f
c4f
c1f
c2f
b1f
--删除测试环境
drop table T
解决方案 »
- Excel表格数据导入SQL2005数据库问题求高手
- 如何在远端数据库上建表呢
- 查询出所有重名的员工信息
- 问个小问题!!!有关触发器触发的问题!
- 问题简单,参与给分,解决追加!!!!急用!我的SQL Server服务管理器老是不启动!!!
- 关于SQL模糊查询
- 请问sqlserver 没有有记录每一条执行过的SQL或T-SQL语句的功能???????
- Left join 中的 on 跟 where 条件到底有什么区别 ,高手进 !!
- 服务器数据库进程消耗内存1.8G是不是太高了
- 俺有一个问题,关于sql server 数据库字段设定的?
- 我的win2000系統怎麼裝不了SQL Server 2000 企業版啊?
- 不能给视图创建触发器
create proc p_Crosstab
@TableName sysname,--表名
@纵轴 sysname,--交叉表最左面的列
@横轴 sysname,--交叉表最上面的列
@表体内容 sysname,--交叉表的数数据字段
@条件 varchar(1000),--查询的处理条件
@是否加横向合计 bit,--为1时在交叉表横向最右边加横向合计
@是否家纵向合计 bit--为1时在交叉表纵向最下边加纵向合计
as
declare @s nvarchar(4000),@sql varchar(8000)--规范条件
set @条件=case when @条件<>'' then ' where ('+@条件+')' else '' end--判断横向字段是否大于纵向字段数目,如果是,则交换纵横字段
set @s='declare @a sysname
if(select case when count(distinct ['+@纵轴+'])<count(distinct ['+@横轴+']) then 1 else 0 end
from ['+@TableName+'] '+@条件+')=1
select @a=@纵轴,@纵轴=@横轴,@横轴=@a'
exec sp_executesql @s
,N'@纵轴 sysname out,@横轴 sysname out'
,@纵轴 out,@横轴 out--生成交叉表处理语句
set @s='
set @s=''''
select @s=@s+'',[''+cast(['+@横轴+'] as varchar)+'']=sum(case ['+@横轴
+'] when ''''''+cast(['+@横轴+'] as varchar)+'''''' then ['+@表体内容+'] else 0 end)''
from ['+@TableName+']
'+@条件+'
group by ['+@横轴+']'
exec sp_executesql @s
,N'@s varchar(8000) out'
,@sql out--是否生成合计字段的处理
declare @sum1 varchar(200),@sum2 varchar(200),@sum3 varchar(200)
select @sum1=case @是否加横向合计
when 1 then ',[合计]=sum(['+@表体内容+'])'
else '' end
,@sum2=case @是否家纵向合计
when 1 then '['+@纵轴+']=case grouping(['
+@纵轴+']) when 1 then ''合计'' else cast(['
+@纵轴+'] as varchar) end'
else '['+@纵轴+']' end
,@sum3=case @是否家纵向合计
when 1 then ' with rollup'
else '' end--生成交叉表
exec('select '+@sum2+@sql+@sum1+'
from ['+@TableName+']
'+@条件+'
group by ['+@纵轴+']'+@sum3)
thanks 真是一个好东东,可惜我现在没时间细细研究。:)另:问题还没解决,谁来帮帮我啊。:(
--成绩统计示例
--测试表
create table #t(xh varchar(3),xm varchar(10),km varchar(10),cj int)
insert into #t
select '001','张三','语文',80
union all select '001','张三','数学',85
union all select '002','李四','语文',90
union all select '002','李四','数学',80
union all select '003','王五','语文',70
union all select '003','王五','数学',78--数据处理
declare @sql nvarchar(4000),@sql1 nvarchar(4000)
select @sql='',@sql1=''
select @sql=@sql+',['+km
+']=sum(case km when '''+km+''' then cj else 0 end)'
,@sql1=@sql1+',['+km+'名次]=(select sum(1) from # where ['
+km+']>=a.['+km+'])'
from(select distinct km from #t) a
exec('select xh 学号,xm 姓名'+@sql+',总成绩=sum(cj)
,总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select *'+@sql1+' from # a
')
drop table #t/*--测试结果学号 姓名 数学 语文 总成绩 总名次 数学名次 语文名次
---- ------ ------- -------- ----------- ----------- ----------- -----------
002 李四 80 90 170 1 2 1
003 王五 78 70 148 3 3 3
001 张三 85 80 165 2 1 2--*/
是不是问交叉表的问题太多,大家都烦了,没仔细看啊!
create table #T (Grade varchar(5),Mt varchar(2))
insert into #T select 'c1f','a1'
insert into #T select 'c2f','a1'
insert into #T select 'c3f','a1'
insert into #T select 'c4f','b1'
insert into #T select 'c1f','a2'
insert into #T select 'c2f','a2'
insert into #T select 'b1f','a2'--执行查询处理过程
select identity(int,1,1) as cid,* into #T1 from #Tdeclare @s varchar(8000)
set @s = ''select
@s = @s + ','+Mt+'=max(case c.Mt when '''+Mt+''' then c.Grade end)'
from
(select Mt,cid=min(cid) from #T1 group by Mt) a
order by
a.cidset @s = 'select '+stuff(@s,1,1,'')
+' from (select a.*,id=count(b.Mt) from #T1 a,#T1 b '
+' where a.Mt=b.Mt and a.cid>=b.cid group by a.Grade,a.Mt,a.cid) c '
+' group by c.id order by c.id'exec(@s)
--输出结果
/*
a1 b1 a2
---- ---- ----
c1f c4f c1f
c2f NULL c2f
c3f NULL b1f
*/
--删除测试数据
drop table #T,#T1
太感谢了(无比激动中.....)激动中断——弱弱的问一句,如何把输出结果插到一张临时表中?