行转列

[hexueying424109]

select
(select sum(c.integral) from score c where c.ctype=s.ctype and c.ctype='数学') as 数学,
(select sum(c.integral) from score c where c.ctype=s.ctype and c.ctype='语文') as 语文
from score s group by s.ctype
这一句错在那里


表结构是
cd ctype integral
1 数学 22
1 语文 33
2 数学 22
2 语文 33
3 数学 22
3 语文 33

[xpingping]

因为你group by s.ctype
试试这个……

select  
   sum(decode(ctype,'数学',integral,0)) as 数学,
   sum(decode(ctype,'语文',integral,0)) as 语文  
from score 

[SKY_4K_PPM]

select
max(decode(ctype,'数学',integral,0)) 数学,
max(decode(ctype,'语文',integral,0)) 语文
from score

[yuna@liu]

--楼主是想获得每个人的各科成绩，还要加上group by
select  cd,
   sum(decode(ctype,'数学',integral,0)) as 数学,
   sum(decode(ctype,'语文',integral,0)) as 语文  
from score group by cd

[hupeng213]

--交叉报表通用转换脚本(行转列)
declare
strTable varchar2(50) := 'hi_dataValue';
firstField varchar2(20) := 'indCode';
--32767为长度上限
strSql varchar2(32700) := '';
strCursorSql varchar2(32700) := '';
--游标相关
TYPE myref IS REF CURSOR;
cur myref;
secondField varchar2(50) := 'varCode';
secondFieldValue varchar2(50) := '';
sumField varchar2(50) := 'indValue';
begin
--分组的第一字段 indCode
strSql := 'select ' || firstField || ', ';
--分组的第二字段,统计第二字段可能出现的实例,拼接字符串
--写法一 使用数组 loop循环实现 分组的第二字段无法使用变量代入
for sumList in (select '
sum(case when varcode = ''' || varcode ||
''' then ' || sumField || ' else 0 end) as F_' ||
substr(varName, 1, 12) || ',' as sumField1
from (/*获取第二个分组字段不重复的列表*/select a.varCode, varName
from (select distinct varcode from hi_datavalue) a
join hi_formula b on a.varCode = b.varCode)) loop
strSql := strSql || sumList.Sumfield1;
end loop;
--表名以及分组语法
strSql := substr(strSql, 1, length(strSql) - 1) ||
' from ' || strTable || ' group by ' || firstField ;
--返回最终交叉报表转换语法
dbms_output.put_line(strSql);

--写法二 使用游标实现
strCursorSql := 'select distinct ' || secondField || ' from ' || strTable;
OPEN cur FOR strCursorSql;
strCursorSql := '';
loop
Fetch cur into secondFieldValue;
Exit when cur%notfound;
strCursorSql := strCursorSql ||'
sum(case when ' || secondField || ' = ''' || secondFieldValue || ''' then ' || sumField ||
' else 0 end) as F_' || substr(secondFieldValue, 1, 12)/*列名只截取12个字符长度*/ || ',';
end loop;
--表名以及分组语法
strCursorSql := 'select ' || firstField || ', ' ||
substr(strCursorSql, 1, length(strCursorSql) - 1) ||
' from ' || strTable || ' group by ' || firstField ;
--返回最终交叉报表转换语法
dbms_output.put_line(strCursorSql);
end;

[888888888888]

楼上的搞大发了。