Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: can I do this in SQL??
A copy of this was sent to "Jorge" <pumuky22_at_usa.net>
(if that email address didn't require changing)
On Thu, 21 Oct 1999 12:16:09 +0200, you wrote:
>Hi everyone, I'm trying to do this select ....
>I have two tables (user_indexes and user_ind_columns) and I want this
>output:
>
>
>index1 column1 column2 column3
>by row
>
>thanks
>
this does the trick:
select substr(a.index_name,1,30) index_name,
decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness,
max(decode( b.column_position, 1, substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 2, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 3, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 4, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 5, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 6, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 7, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 8, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 9, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 10, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 11, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 12, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 13, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 14, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 15, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 16, ', '||substr(b.column_name,1,30), NULL ))colsfrom all_indexes a, all_ind_columns b
and a.table_name = :tname and b.table_name = a.table_name and b.table_owner = a.owner and a.index_name = b.index_name
--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 22 1999 - 08:11:47 CDT