Creating Comma Seperated Lists - Revision history

WikiSysop at 20:00, 18 June 2013

2013-06-18T20:00:38Z

← Older revision		Revision as of 20:00, 18 June 2013
Line 1:		Line 1:
	== Comma Seperated Column from a List of Rows ==		== Comma Seperated Column from a List of Rows ==

-	If you have a table 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:	+	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"		{\| class="wikitable"

WikiSysop at 23:48, 24 November 2011

2011-11-24T23:48:51Z

← Older revision		Revision as of 23:48, 24 November 2011
Line 35:		Line 35:
	order by owner,table_name		order by owner,table_name

-	OWNER TABLE_NAME COLUMNS	+	OWNER TABLE_NAME COLUMNS
-	----- ------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	+	----- ---------------------------- -------------------------------------------------------------------
-	SYS ~~DBA_TABLESPACES~~ TABLESPACE_NAME,~~BLOCK_SIZE~~,~~INITIAL_EXTENT~~,~~NEXT_EXTENT~~,~~MIN_EXTENTS~~,~~MAX_EXTENTS~~,~~MAX_SIZE~~,~~PCT_INCREASE~~,~~MIN_EXTLEN~~,~~STATUS~~,~~CONTENTS~~,~~LOGGING~~,~~FORCE_LOGGING~~,~~EXTENT_MANAGEMENT~~,~~ALLOCATION_TYPE~~,~~PLUGGED_IN~~,~~SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION,BIGFILE,PREDICATE_EVALUATION,ENCRYPTED,COMPRESS_FOR~~	+	SYS DBA_TABLESPACE_GROUPS GROUP_NAME,TABLESPACE_NAME
-	SYS ~~DBA_TABLESPACE_GROUPS GROUP_NAME~~,~~TABLESPACE_NAME~~	+	SYS DBA_TABLESPACE_USAGE_METRICS TABLESPACE_NAME,USED_SPACE,TABLESPACE_SIZE,USED_PERCENT
-	~~SYS DBA_TABLESPACE_THRESHOLDS TABLESPACE_NAME~~,~~CONTENTS~~,~~EXTENT_MANAGEMENT,THRESHOLD_TYPE,METRICS_NAME,WARNING_OPERATOR,WARNING_VALUE,CRITICAL_OPERATOR,CRITICAL_VALUE~~	+	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>		</pre>
Line 51:		Line 51:
	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 ~~column_id~~) rn	+	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
	)		)
Line 60:		Line 60:
	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>		</pre>

Line 76:		Line 86:

	set colsep " "		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>		</pre>

WikiSysop at 23:29, 24 November 2011

2011-11-24T23:29:50Z

← Older revision		Revision as of 23:29, 24 November 2011
Line 34:		Line 34:
	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_TABLESPACES TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,MAX_SIZE,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,FORCE_LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLUGGED_IN,SEGMENT_SPACE_MANAGEMENT,DEF_TAB_COMPRESSION,RETENTION,BIGFILE,PREDICATE_EVALUATION,ENCRYPTED,COMPRESS_FOR
		+	SYS DBA_TABLESPACE_GROUPS GROUP_NAME,TABLESPACE_NAME
		+	SYS DBA_TABLESPACE_THRESHOLDS TABLESPACE_NAME,CONTENTS,EXTENT_MANAGEMENT,THRESHOLD_TYPE,METRICS_NAME,WARNING_OPERATOR,WARNING_VALUE,CRITICAL_OPERATOR,CRITICAL_VALUE
		+
		+
		+
		+
	</pre>		</pre>
		+
		+
	...and this one puts the columns in alphabetical order:		...and this one puts the columns in alphabetical order:
	<pre>		<pre>

WikiSysop at 23:07, 24 November 2011

2011-11-24T23:07:45Z

← Older revision		Revision as of 23:07, 24 November 2011
Line 29:		Line 29:
	)		)
	start with rn = 1		start with rn = 1
-	connect by prior rn = rn-1	+	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 43:		Line 43:
	)		)
	start with rn = 1		start with rn = 1
-	connect by prior rn = rn-1	+	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 52:		Line 52:
	== Query Results into Comma Separated Values ==		== Query Results into Comma Separated Values ==

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

	<pre>		<pre>

WikiSysop at 22:58, 22 November 2011

2011-11-22T22:58:38Z

← Older revision		Revision as of 22:58, 22 November 2011
Line 52:		Line 52:
	== Query Results into Comma Separated Values ==		== Query Results into Comma Separated Values ==

-	In SQL Plus, a CSV file can be created from the results of a query by ~~adding a~~	+	In SQL Plus, a CSV file can be created from the results of a query by chnaging the column separation (colsep):

	<pre>		<pre>

WikiSysop at 22:54, 22 November 2011

2011-11-22T22:54:47Z

← Older revision		Revision as of 22:54, 22 November 2011
Line 1:		Line 1:
		+	== Comma Seperated Column from a List of Rows ==
		+
	If you have a table 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:		If you have a table 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"		{\| class="wikitable"
	\|-		\|-
		+	! Owner
	! Table Name		! Table Name
	! Column Names		! Column Names

	\|-		\|-
		+	\|EXAMPLE
	\|ISO_COUNTRY		\|ISO_COUNTRY
	\|CODE,NAME		\|CODE,NAME
		+
		+	\|-
		+	\|SYS
		+	\|DBA_SYNONYMS
		+	\|OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME,DB_LINK

	\|}		\|}
Line 39:		Line 48:
	group by owner,table_name		group by owner,table_name
	order by owner,table_name		order by owner,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 adding a
		+
		+	<pre>
		+	rem Creates a CSV file from a SQL query
		+
		+	set colsep ,
		+
		+	spool countries.csv
		+	select * from ISO_COUNTRY;
		+	spool off
		+
		+	set colsep " "
	</pre>		</pre>

WikiSysop at 21:48, 13 November 2011

2011-11-13T21:48:41Z

← Older revision		Revision as of 21:48, 13 November 2011
Line 4:		Line 4:
	\|-		\|-
	! Table Name		! Table Name
-	! Column ~~Name~~	+	! Column Names

	\|-		\|-

WikiSysop at 20:29, 20 August 2011

2011-08-20T20:29:43Z

← Older revision		Revision as of 20:29, 20 August 2011
Line 26:		Line 26:
	order by owner,table_name		order by owner,table_name
	</pre>		</pre>
-	...and this one puts ~~them~~ in alphabetical order:	+	...and this one puts the columns in alphabetical order:
	<pre>		<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

WikiSysop at 09:41, 20 August 2011

2011-08-20T09:41:23Z

← Older revision		Revision as of 09:41, 20 August 2011
Line 1:		Line 1:
		+	If you have a table 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"
		+	\|-
		+	! Table Name
		+	! Column Name
		+
		+	\|-
		+	\|ISO_COUNTRY
		+	\|CODE,NAME
		+
		+	\|}
		+
	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:		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>		<pre>

WikiSysop at 23:15, 18 July 2011

2011-07-18T23:15:24Z

← Older revision		Revision as of 23:15, 18 July 2011
Line 1:		Line 1:
	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:		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 12:		Line 12:
	group by owner,table_name		group by owner,table_name
	order by owner,table_name		order by owner,table_name
-		+	</pre>
	...and this one puts them in alphabetical order:		...and this one puts them 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 (
Line 26:		Line 26:
	group by owner,table_name		group by owner,table_name
	order by owner,table_name		order by owner,table_name
		+	</pre>