Difference between revisions of "Creating Comma Seperated Lists"

Latest revision as of 19:04, 7 April 2016

We cover two ways to get CSV results from Oracle Database - to convert a query to CSV, and an example of how to flatten rows into a CSV string.

Query Results into Comma Separated Values

In SQL Plus, the results of a SQL query can be output as a CSV file by changing the column separation (colsep):

rem Creates a CSV file from a SQL query

set colsep ,

spool countries.csv
select * from ISO_COUNTRY;
spool off

set colsep " "

CO,NAME
--,---------------------
AF,AFGHANISTAN
AL,ALBANIA
DZ,ALGERIA
AS,AMERICAN SAMOA
AD,ANDORRA
AO,ANGOLA
AI,ANGUILLA
AQ,ANTARCTICA
AG,ANTIGUA AND BARBUDA

Comma Seperated Column from a List of Rows

If you have a table in an Oracle Database that contains groups of lists - such as the list of columns in tables - and you want to list the columns against their table name on one line rather than across several - such as:

Creating a comma separated list from a table can be done by using the tree function sys_connect_by_path - this example creates a list of the columns in each table or view:

select 
owner
,table_name
,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
from (
	select owner,table_name,column_name,column_id rn
	from sys.dba_tab_columns t
)
start with rn = 1
connect by prior rn+1 = rn
and prior table_name=table_name
and prior owner=owner
group by owner,table_name
order by owner,table_name

OWNER TABLE_NAME                   COLUMNS
----- ---------------------------- -------------------------------------------------------------------
SYS   DBA_TABLESPACE_GROUPS        GROUP_NAME,TABLESPACE_NAME
SYS   DBA_TABLESPACE_USAGE_METRICS TABLESPACE_NAME,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
SYS   DBA_TAB_COMMENTS             OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS
SYS   DBA_TAB_PRIVS                GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,GRANTABLE,HIERARCHY
SYS   DBA_TAB_STATS_HISTORY        OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,STATS_UPDATE_TIME
SYS   DBA_TAB_STAT_PREFS           OWNER,TABLE_NAME,PREFERENCE_NAME,PREFERENCE_VALUE

...and this one puts the columns in alphabetical order:

select 
owner
,table_name
,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
from (
	select owner,table_name,column_name,row_number() over (partition by table_name order by column_name) rn
	from sys.dba_tab_columns t
)
start with rn = 1
connect by prior rn+1 = rn
and prior table_name=table_name
and prior owner=owner
group by owner,table_name
order by owner,table_name

OWNER TABLE_NAME                   COLUMNS
----- ---------------------------- -------------------------------------------------------------------
SYS   DBA_TABLESPACE_GROUPS        GROUP_NAME,TABLESPACE_NAME
SYS   DBA_TABLESPACE_USAGE_METRICS TABLESPACE_NAME,TABLESPACE_SIZE,USED_PERCENT,USED_SPACE
SYS   DBA_TAB_COMMENTS             COMMENTS,OWNER,TABLE_NAME,TABLE_TYPE
SYS   DBA_TAB_PRIVS                GRANTABLE,GRANTEE,GRANTOR,HIERARCHY,OWNER,PRIVILEGE,TABLE_NAME
SYS   DBA_TAB_STATS_HISTORY        OWNER,PARTITION_NAME,STATS_UPDATE_TIME,SUBPARTITION_NAME,TABLE_NAME
SYS   DBA_TAB_STAT_PREFS           OWNER,PREFERENCE_NAME,PREFERENCE_VALUE,TABLE_NAME