用函数实现: 表名为A。 CREATE OR REPLACE FUNCTION get_field(KEY VARCHAR2) RETURN VARCHAR2 IS col_field VARCHAR2(4000); BEGIN FOR cur IN (SELECT field2 FROM A WHERE field1= KEY) LOOP col_field := col_field||' '||cur.field2; END LOOP; col_field := ltrim(col_field,' '); RETURN col_field; END; /SELECT DISTINCT field1,get_field(field1) field2 FROM A;但是不能生成field2 field3 field4这些不定列,只能显示组合起来的数据,如果页面需要展示列名,必须进行适合的处理,如预定一些列头。 不然也不能同时展示多条类似的记录。 如: field1 field2 field3 field4 filed5 001 tw1 tw2 tw3 002 tw1 tw2 tw3 tw4 003 tw1 tw2
select field1,max(decode(index_all,'1',field2)) a, max(decode(index_all,'2',field2)) b, max(decode(index_all,'3',field2)) c from (select field1,field2, row_number() over (partition by field1 order by field2) index_all from testh group by field1,field2 ) group by field1
表名为A。
CREATE OR REPLACE FUNCTION get_field(KEY VARCHAR2)
RETURN VARCHAR2
IS
col_field VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT field2 FROM A WHERE field1= KEY) LOOP
col_field := col_field||' '||cur.field2;
END LOOP;
col_field := ltrim(col_field,' ');
RETURN col_field;
END;
/SELECT DISTINCT field1,get_field(field1) field2 FROM A;但是不能生成field2 field3 field4这些不定列,只能显示组合起来的数据,如果页面需要展示列名,必须进行适合的处理,如预定一些列头。
不然也不能同时展示多条类似的记录。
如:
field1 field2 field3 field4 filed5
001 tw1 tw2 tw3
002 tw1 tw2 tw3 tw4
003 tw1 tw2
max(decode(index_all,'2',field2)) b,
max(decode(index_all,'3',field2)) c
from
(select field1,field2,
row_number() over (partition by field1 order by field2) index_all
from testh
group by field1,field2
)
group by field1