01 November 2016

get column result as a row

Today, I had to create a single row from:

Select tablespace_name from dba_tablespaces ;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS


This can easily be done with (11.2 and up):

select contents,listagg(TABLESPACE_NAME,',')
  within group
  (order by CONTENTS) my_tbs
  from dba_tablespaces
  group by contents;


CONTENTS  MY_TBS
--------- ---------------------------------------------
PERMANENT SYSAUX,SYSTEM,USERS

TEMPORARY TEMP
UNDO      UNDOTBS1


This works nice!