Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle Function Listagg
WITH q_tab AS 
(SELECT level idx, DECODE(MOD(level,2),0,'EVEN','ODD') num_type
 FROM DUAL CONNECT BY level <=10)
SELECT 
 num_type,
 CAST(LISTAGG(idx,', ') WITHIN GROUP (ORDER BY idx) 
  AS VARCHAR2(30)) num_type_list
FROM q_tab
GROUP BY num_type
ORDER BY num_type DESC;

Oracle 11g LISTAGG output

SELECT
  uidx.table_name
 ,uidx.index_name
 ,LISTAGG(ucc.column_name,', ')
   WITHIN GROUP (ORDER BY ucc.position)  index_columns,
 ,uidx.uniqueness
FROM  user_indexes uidx
JOIN  user_con_columns ucc
ON    uidx.index_name = ucc.constraint_name
wHERE  uidx.table_name = UPPER('&i_table')
AND    ucc.constraint_name = UPPER('&i_constraint')
GROUP BY
  uidx.table_name
 ,uidx.index_name
 ,uidx.uniqueness
;

Oracle COUNT(*) OVER() to get similar output    XMLAGG

  Oracle 19c - LISTAGG

  Oracle 12c Release 2 (12.2) - LISTAGG

Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 13th, 2021

  18048