表结构如下(Table):
--------------------------------------------------
id name Fname
1 a1 bb
1 a1 cc
2 a2 bbb
2 a2 ccc
2 a3 ddd
……怎样输入下面形式的结果集呢?
id name Fname
1 a1 bb/cc
2 a2 bbb/ccc/ddd
……
--------------------------------------------------
id name Fname
1 a1 bb
1 a1 cc
2 a2 bbb
2 a2 ccc
2 a3 ddd
……怎样输入下面形式的结果集呢?
id name Fname
1 a1 bb/cc
2 a2 bbb/ccc/ddd
……
id name Fname
1 a1 bb/cc
2 a2 bbb/ccc/ddd
……
select id, Fname, colc, lead(colc) over(partition by id order by colc) cold from (
select id, Fname, row_number() over(order by id, Fname) colc from tab) )
start with cold is null
connect by prior colc=cold
group by id;
sasacat(傻傻猫) 的在9i下才得行!!
function GetSameIDString(iID number)
as
声明一个游标
begin
把游标里的字符串起来
end GetSameIDString;
然后
SELECT tmp.id, tmp.name, GetSameIDString(tmp.id) as Fname
FROM (SELECT id,name FROM Table group by id) tmp
不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成,这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可