Re: can I do this in SQL??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 22 Oct 1999 09:11:47 -0400
Message-ID: <NWIQOCJCoPR7AU0OtIGMsq19Q5Ef@4ax.com>

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 ))cols

from all_indexes a, all_ind_columns b
where a.owner = :owner

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

group by substr(a.index_name,1,30), a.uniqueness /

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