CREATE OR REPLACE PROCEDURE row2col(o OUT SYS_REFCURSOR) IS sqlstr VARCHAR2(4000) := ''; BEGIN FOR cc IN (SELECT distinct itemname FROM TestRowToColumn) LOOP sqlstr := sqlstr || 'max(decode(itemname,''' || cc.itemname ||''',itemvalue)) as "' || cc.itemname || '",'; END LOOP; sqlstr := 'select line,' || rtrim(sqlstr,',') || ' from TestRowToColumn group by line'; dbms_output.put_line(sqlstr); OPEN o FOR sqlstr; execute immediate sqlstr; END row2col; 坛子里太多,自己搜索。给个简单。
那這個可以滿足
http://zhidao.baidu.com/question/101604512.html?fr=ala0
CREATE OR REPLACE PROCEDURE row2col(o OUT SYS_REFCURSOR) IS
sqlstr VARCHAR2(4000) := '';
BEGIN
FOR cc IN (SELECT distinct itemname FROM TestRowToColumn)
LOOP
sqlstr := sqlstr || 'max(decode(itemname,''' || cc.itemname ||''',itemvalue)) as "'
|| cc.itemname || '",';
END LOOP;
sqlstr := 'select line,' || rtrim(sqlstr,',') || ' from TestRowToColumn group by line';
dbms_output.put_line(sqlstr);
OPEN o FOR sqlstr;
execute immediate sqlstr;
END row2col;
坛子里太多,自己搜索。给个简单。
不过这只是我自己的看法,不知道你用的DBMS中有没有这样的功能,不过目前还没听过
select '张三' name from dual
union all
select '李四' from dual
)
select replace(wm_concat(name),',',' ') name from tab ;
-----------
张三 李四
张三
李四你的表就一列????
select max(col1),max(col2) from (
select decode(rownum,1,sname,null) as col1,decode(rownum,2,sname,null)as col2 from a
)