Creating Comma Seperated Lists - Revision history

WikiSysop at 19:04, 7 April 2016

2016-04-07T19:04:10Z

WikiSysop at 19:01, 7 April 2016

2016-04-07T19:01:36Z

WikiSysop at 19:00, 7 April 2016

2016-04-07T19:00:16Z

WikiSysop: 1 revision imported

2016-03-25T08:51:03Z

1 revision imported

WikiSysop at 20:00, 18 June 2013

2013-06-18T20:00:38Z

New page

== 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:

{| class="wikitable"
|-
! Owner
! Table Name
! Column Names

|-
|EXAMPLE
|ISO_COUNTRY
|CODE,NAME

|-
|SYS
|DBA_SYNONYMS
|OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK

|}

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:
<pre>
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

</pre>

...and this one puts the columns in alphabetical order:
<pre>
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

</pre>

== Query Results into Comma Separated Values ==

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

<pre>
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

</pre>

← Older revision		Revision as of 19:01, 7 April 2016
Line 1:		Line 1:
−	We cover two ways to get CSV results from [[Oracle]] - to convert a query to CSV, and an example of how to flatten rows into a CSV string.	+	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 ==		== Query Results into Comma Separated Values ==

@@ Line 55: / Line 55: @@
 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:
 <pre>
-select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
+select
 from (
 	select owner,table_name,column_name,column_id rn
@@ Line 81: / Line 84: @@
 ...and this one puts the columns in alphabetical order:
 <pre>
-select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns
+select
 from (
 	select owner,table_name,column_name,row_number() over (partition by table_name order by column_name) rn

@@ Line 1: / Line 1: @@
 == Comma Seperated Column from a List of Rows ==
@@ Line 69: / Line 101: @@
 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
 </pre>

← Older revision	Revision as of 08:51, 25 March 2016
(No difference)