Horizontal Output from ORACLE SQL

uform — Tue, 20 Feb 2007 13:36:07 GMT

Hi,

I have around 200 columns in a table.
I'm using all_tab_columns to fetch the column names. which displays
Col1
Col2
Col3
etc...

I need the output as
Col1,Col2,Col3...how to do this ?

Thanks

Re: Horizontal Output from ORACLE SQL

Sandman! — Tue, 20 Feb 2007 13:42:45 GMT

Imho it's possible to do this only through a stored procedure on an anonymous PL/SQL block. Don't think SQL*Plus can do this for you.

~cheers

Re: Horizontal Output from ORACLE SQL

Hein van den Heuvel — Tue, 20 Feb 2007 22:43:09 GMT

I could solve that problem in a few lines of line of easy perl... or 50 lines of wild and wacky sql.

Perl first:

foreach (`sqlplus -s user/password \@tab_cols.sql`) {
my($table,$col) = split;
if ($table ne $old) {
if ($old) {
print "$old $cols\n";
}
$cols = $col;
$old = $table;
} else {
$cols .= "," . $col;
}
}
# Last blank line triggers last table output.

Where tab_cols.sql reads:

set pages 0 lines 9999 head off feedbac off
select table_name, column_name from user_tab_columns order by table_name, column_id;
exit

Now for SQL there is this newfangled (Oracle 10g or better !) MODEL function, which is like a PIVOT TABLE.
I found a usage example in the "SQL COOKBOOK"
Chapter 11, "Creating CVS output from Oracle"
http://www.oreilly.com/catalog/sqlckbk/

I adapted this to use USER_TAB_COLUMNS as follows:

select table_name, list "Columns..."
from (
select *
from (
select table_name, column_id, column_name,
lag (table_name) over (partition by table_name order by column_id) prior_table
from user_tab_columns
)
model
dimension by (
table_name, row_number() over(partition by table_name order by column_id) rn
)
measures (
column_name, prior_table, cast(null as varchar2(500)) list,
count(*) over (partition by table_name) cnt,
row_number() over(partition by table_name order by column_id) rnk
)
rules (
list[any,any]
order by table_name, rn =
case
when prior_table[cv(),cv()] is null
then column_name[cv(),cv()]
else column_name[cv(),cv()] || ',' || list[cv(),rnk[cv(),cv()]-1]
end
)
)
where cnt = rn;

There is no way I could have made that up!
I just modified the template.

The result for a test account on a 10g XE install on my PC is an (amazing!)...

SQL> @csv.sql

TABLE_NAME Columns...
------------------------------ ----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
---
BIN$UTvAkVYzQ0WadVJQMNzvGg==$0 C,B,A
MY_PLAN_TABLE FILTER_PREDICATES,ACCESS_PREDICATES,TEMP_SPACE,IO_COST,CPU_COST,DISTRIBUTION,OTHER,PARTITION_ID,PART
TION_STOP,PARTITION_START,OTHER_TAG,BYTES,CARDINALITY,COST,POSITION,PARENT_ID,ID,SEARCH_COLUMNS,OPTIMIZER,OBJECT_TYPE,OBJECT_INSTAN
E,OBJECT_NAME,OBJECT_OWNER,OBJECT_NODE,OPTIONS,OPERATION,REMARKS,TIMESTAMP,STATEMENT_ID
TEST TEST
VT_FEATURE DESCRIPTION,FEATURE
VT_FEATURE_BASE DESCRIPTION,FEATURE

x rows selected.

SQL>

For specific tables, change that "from user_tab_columns" in the middle to "from user_tab_columns where table_name like '%....%'"

Regards,
Hein van den Heuvel
HvdH Performance Consulting

Re: Horizontal Output from ORACLE SQL

Subodh deshpande_1 — Wed, 21 Feb 2007 01:29:03 GMT

Hi,
Why dont you connect to Oracle through MS EXCEL. This will help you.

You should have system admin privileges.
Connect to ODBC.
Choose system DSN
Click on ADD button
Choose Oracle datasource
enter the required entries.
and test connection.
Go to excel
Choose Data-->Query database.

I think this will help you.

Regards
Subodh

topic Re: Horizontal Output from ORACLE SQL in Operating System - HP-UX

Horizontal Output from ORACLE SQL

Re: Horizontal Output from ORACLE SQL

Re: Horizontal Output from ORACLE SQL

Re: Horizontal Output from ORACLE SQL