안녕하세요
질문 좀 드릴께요
all_tab_columns 를 보면 테이블에 대한 컬럼정보를 볼 수있는데요
결과를 아래와 같이 뽑을라면 어떻게 하나요?
<결과>
table_name col_name
aa_table a_col,b_col,c_col,d_col
bb_table z_col,e_col,f_col
이렇게 테이블별 컬럼을 한 줄로 표시요
select table_name , wm_concat(column_name) col_name from all_tab_columns where owner = '......' and table_name like '...%' group by table_name
select table_name , substr(xmlagg(xmlelement(x,',', column_name) order by column_id).extract('//text()'), 2) column_name from all_tab_columns where owner = ....... and table_name like '......%' group by table_name
select table_name , substr(sys_connect_by_path(column_name, ','), 2) column_name from ( select table_name , column_name , column_id , count(column_id) over(partition by table_name) max_col from all_tab_columns where owner = ...... and table_name like '.....%' ) where level = max_col start with column_id = 1 connect by prior table_name = table_name and prior column_id = column_id - 1 ;
