急求一个竖转横的sql语句,在线等待急....(50分)

  • 主题发起人 主题发起人 新来的菜鸟
  • 开始时间 开始时间

新来的菜鸟

Unregistered / Unconfirmed
GUEST, unregistred user!
我想把以下表的数据转成横项的,该如何转???<br><br>&nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A2 &nbsp; &nbsp; &nbsp; A3 &nbsp; &nbsp;A4 &nbsp; A5<br><br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;语文 &nbsp;101<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;语文 &nbsp;109<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;语文 &nbsp;100<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;数学 &nbsp;101<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;数学 &nbsp; 85<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;数学 &nbsp; 58<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;政治 &nbsp; 56<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;政治 &nbsp; 56<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;政治 &nbsp; 50<br> <br> 我想把以上的表转成<br> <br> <br>&nbsp; &nbsp; &nbsp; &nbsp; A1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A2 &nbsp; &nbsp; &nbsp; A3 &nbsp; &nbsp;A4 &nbsp; A5<br>&nbsp; &nbsp; &nbsp;考试项目 &nbsp; &nbsp; &nbsp; 学号 &nbsp; &nbsp; 姓名 &nbsp; 语文 &nbsp; 数学 &nbsp;政治<br>&nbsp; &nbsp; &nbsp;<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp; 101 &nbsp; &nbsp; 101 &nbsp; 56<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp; 109 &nbsp; &nbsp; 85 &nbsp; &nbsp;56<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp; 100 &nbsp; &nbsp; 58 &nbsp; &nbsp;50<br> <br>但注意的是科目是不固定的也就是说如果他再增加科目的时候而横项也会自动增加 <br><br>例如,如果加了英语<br><br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;语文 &nbsp;101<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;语文 &nbsp;109<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;语文 &nbsp;100<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;数学 &nbsp;101<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;数学 &nbsp; 85<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;数学 &nbsp; 58<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;政治 &nbsp; 56<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;政治 &nbsp; 56<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;政治 &nbsp; 50<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp;英语 &nbsp; 102<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp;英语 &nbsp; 110<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp;英语 &nbsp; 93<br> <br> 那么就应该变为<br> <br> <br> 考试项目 &nbsp; &nbsp; &nbsp; 学号 &nbsp; &nbsp; 姓名 &nbsp; 语文 &nbsp; 数学 &nbsp;政治 &nbsp; 英语<br>&nbsp; &nbsp; &nbsp;<br> 2006级期末考 &nbsp; 20080101 &nbsp;张三 &nbsp; 101 &nbsp; &nbsp; 101 &nbsp; 56 &nbsp; 102<br> 2006级期末考 &nbsp; 20080202 &nbsp;李四 &nbsp; 109 &nbsp; &nbsp; 85 &nbsp; &nbsp;56 &nbsp; 110<br> 2006级期末考 &nbsp; 20080303 &nbsp;王五 &nbsp; 100 &nbsp; &nbsp; 58 &nbsp; &nbsp;50 &nbsp; &nbsp;93<br> <br> <br> 我该如何转呢?请各位大哥帮帮忙小弟急用,万分感谢!
 
这种问题目前好像没有直接的语句来实现,我查过很多资料了。<br>提供一种办法,很麻烦,但是可以用一个语句就可以实现了,最好做视图:<br>Select 考试项目,学号,姓名,Sum(语文) As 语文,Sum(数学) As 数学,Sum(政治) As 政治,Sum(英语) As 英语 From (<br>Select 考试项目,学号,姓名,语文,0 As 数学,0 As 政治,0 As 英语 From Table<br>Union All<br>Select 考试项目,学号,姓名,0 As 语文,数学,0 As 政治,0 As 英语 From Table<br>Union All<br>Select 考试项目,学号,姓名,0 As 语文,0 As 数学,政治,0 As 英语 From Table<br>Union All<br>Select 考试项目,学号,姓名,0 As 语文,0 As 数学,0 As 政治,英语 From Table)TT<br>Group BY 语文,数学,政治,英语
 
這個,你用SQL語句寫也不難啊<br>先查出有多少個科目,然後動態的建立一個臨時表,字段中,各個科目字段你要用態的用SQL語句建立,然後寫SQL語句,將當然表中的數據插入到臨時表中去.不難的啊<br><br>關鍵是動態的為臨時表建立字段,不過這個不難啊<br>alter table add column
 
目前还没有这种交叉查询的实现办法,关注一下吧...
 
既然你是以学科的形式出现,毕竟学科就这么几种,最笨的方法你先把所有学科的做到表上,然后把你原来的数据编个简单的程序全部写到新表,再根据你需要select出你需要的那些学科,不就行了。。。
 
declare @sql varchar(8000)<br><br>set @sql = ' case'<br><br><br>select @sql =@sql+' when '+科目+' then 分數,' &nbsp;from 表名 <br>set @sql =substring(@sql,1,len(@sql)-1)<br>set @sql=( 'select 考试项目,学号, 姓名'+@sql+' end from 表名');<br>exec(@sql)<br>好人做到底.這下應該可以了
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CrossTab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)<br>drop procedure [dbo].[CrossTab]<br>GO<br><br>SET QUOTED_IDENTIFIER ON <br>GO<br>SET ANSI_NULLS ON <br>GO<br><br><br><br>CREATE &nbsp;procedure CrossTab <br>@strTabName as varchar(50), --此处放表名<br>@strCol as varchar(50) , &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; --表头分组依据字段<br>@strGroup as varchar(50) ,--分组字段<br>@strNumber as varchar(50) , &nbsp; &nbsp;--被统计的字段<br>@strSum as varchar(10) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;--运算方式<br>AS<br><br><br>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aaslys10]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br>drop table [dbo].[aaslys10]<br><br>DECLARE @strSql as varchar(5000), @strTmpCol as varchar(100)<br>EXECUTE ('DECLARE cross_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标<br>begin<br>&nbsp; SET nocount ON <br>&nbsp; --SET @strsql ='select ' + @strGroup + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段<br>&nbsp; SET @strsql ='select ' + @strGroup &nbsp;--+ @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段<br><br>&nbsp; OPEN cross_cursor<br>&nbsp; while (0=0)<br>&nbsp; BEGIN<br>&nbsp; &nbsp; FETCH NEXT FROM cross_cursor --遍历游标,将列头信息放入变量@strTmpCol<br>&nbsp; &nbsp; INTO @strTmpCol<br>&nbsp; &nbsp; if (@@fetch_status&lt;&gt;0) break<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SET @strsql = @strsql + ', ' + &nbsp;@strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + rtrim(@strTmpCol) + ']' --构造查询<br>&nbsp; END<br>&nbsp; &nbsp; &nbsp; &nbsp; SET @strsql = @strsql + ' into aaslys10 from ' + @strTabname + ' group by ' + @strGroup --查询结尾<br><br>&nbsp; EXECUTE(@strsql) --执行<br><br>--print @strsql<br><br><br>&nbsp; <br>&nbsp; CLOSE cross_cursor <br>&nbsp; DEALLOCATE cross_cursor RETURN 0 --释放游标,返回0表示成功<br>&nbsp; IF @@error &lt;&gt;0 RETURN @@error --如果出错,返回错误代码<br>end<br><br><br>GO<br>SET QUOTED_IDENTIFIER OFF <br>GO<br>SET ANSI_NULLS ON <br>GO<br><br>--可以在查询分析器里执行一下,这个存储过程.<br>exec '表名','科目','学生','分数','Max'<br>select * from aaslys10<br>--这里建立了一个临时表aaslys10
 
这我也是,当时在网上找的资料,就存下来了,看到同志们需要,我就共享一下了!
 
这个问题我好象回答过,帮你找找
 
呵呵,就是你提的问题嘛,<br>一个SQL语句是解决不了问题的,你用的ACCSS还是SLQSERVER,还是ORACLE
 
select a as 考试项目, b as 学号,c as 姓名,d as 语文,e as数学,f as 政治,g as 英语 <br>&nbsp; from (select a as 考试项目, 0 as 学号,0 as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 where a = '2006级期末考' union all select 0 as 考试项目, b as 学号,0 as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 where b = '20080101' union all select 0 as 考试项目, 0 as 学号,c as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 where c = '张三' &nbsp;union all.........省略 )
 
其中 a,b,c...分别代表对应考试项目,学号,姓名...的字段名
 
select a as 考试项目, b as 学号,c as 姓名,d as 语文,e as数学,f as 政治,g as 英语 <br>&nbsp; from (select a as 考试项目, 0 as 学号,0 as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 from table where a = '2006级期末考' union all select a as 考试项目, b as 学号,0 as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 from table where a = '2006级期末考' union all select a as 考试项目, 0 as 学号,c as 姓名,0 as 语文,0 as数学,0 as 政治,0 as 英语 from table where a = '2006级期末考' &nbsp;union all.........省略 )修正一下,后面的条件不需要,如果按后面,如学号,姓名来查,自己看着加,这实际上就是个交叉表问题,你看看这方面的例子
 
用存储过程是唯一的办法,因为科目是不确定的,所以只能按yunxi126,xiaoping622的思路来做,其他人写的都不对的。因为科目是不确定的!
 
有多少科目你就uion多少,这是最简单的,一般报表或者GRID显示都可以满足<br>因为报表和grid格式大多是死的
 
科目不确定没关系阿<br>按最多的来 你没有这个科目uion也没关系 因为查不出因为uion多出科目的成绩<br>这种问题 1、单靠一句SQL来完成 uion来uion去的 即使可以效率也不高<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2、利用临时表 写个储存过程或函数完成 &nbsp;或就通过程序来处理,加个类似正在汇总的界面忽悠下就好了
 
我建议你写一个程序来做,那样会方便的多。
 
来自:fr9019, 时间:2008-11-12 20:36:21, ID:3929714<br>有多少科目你就uion多少,这是最简单的,一般报表或者GRID显示都可以满足<br>因为报表和grid格式大多是死的<br><br>科目不确定你怎么UNION阿?<br>也就是说这个UNION也是拼出来的才行,通过游标来循环,就可以了。
 
多人接受答案了。
 
后退
顶部