Creating Comma Seperated Lists
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Creating Comma Seperated Lists
(Created page with "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: selec...") |
|||
| Line 1: | Line 1: | ||
| - | + | == 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 | select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns | ||
from ( | from ( | ||
| Line 7: | Line 29: | ||
) | ) | ||
start with rn = 1 | start with rn = 1 | ||
| - | connect by prior rn = rn | + | connect by prior rn+1 = rn |
and prior table_name=table_name | and prior table_name=table_name | ||
and prior owner=owner | and prior owner=owner | ||
| Line 13: | Line 35: | ||
order 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 | select owner,table_name,max(ltrim(sys_connect_by_path(column_name,','),',')) Columns | ||
from ( | from ( | ||
| - | select owner,table_name,column_name,row_number() over (partition by table_name order by | + | select owner,table_name,column_name,row_number() over (partition by table_name order by column_name) rn |
from sys.dba_tab_columns t | from sys.dba_tab_columns t | ||
) | ) | ||
start with rn = 1 | start with rn = 1 | ||
| - | connect by prior rn = rn | + | connect by prior rn+1 = rn |
and prior table_name=table_name | and prior table_name=table_name | ||
and prior owner=owner | and prior owner=owner | ||
group by owner,table_name | group by owner,table_name | ||
order 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> | ||
