Xref: alice comp.databases.oracle.marketplace:531860 comp.databases.oracle.misc:45039 comp.databases.oracle.server:71006 comp.databases.oracle.tools:34272
Path: alice!news-feed.fnsi.net!newsfeed.icl.net!newsfeed.icl.net!news.algonet.se!algonet!newsfeed1.online.no!newsfeed.online.no!Norway.EU.net!uio.no!juliett.dax.net!not-for-mail
From: "John David Birch" <john.birch@usa.net>
Newsgroups: comp.databases.oracle.marketplace,comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
References: <7umovi$t98$1@diana.bcn.ttd.net>
Subject: Re: can I do this in SQL??
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <zSYP3.148$OO.4220@juliett.dax.net>
Date: Fri, 22 Oct 1999 12:35:43 GMT
X-Complaints-To: abuse@tele2.no
X-Trace: juliett.dax.net 940595743 193.217.7.2 (Fri, 22 Oct 1999 14:35:43 MET DST)
NNTP-Posting-Date: Fri, 22 Oct 1999 14:35:43 MET DST
Organization: Tele2 Norway AS Public Access

yes, certainly. Here is a little query to get you going: (It handles upto 4
members but you can see the pattern and how to extend it:

select  i.index_name index_name,
(
 select c1.column_name  from user_ind_columns c1
 where  i.index_name=c1.index_name
 AND rownum<=1
) column_name_1,
(
 select c2.column_name  from user_ind_columns c2
 where  i.index_name=c2.index_name
 AND not c2.column_name in
 ( select c2a.column_name from user_ind_columns c2a
   where i.index_name=c2a.index_name
 AND rownum<2 )
 AND rownum=1
) column_name_2,
(
 select c3.column_name from user_ind_columns c3
 where  i.index_name=c3.index_name
 AND NOT c3.column_name in
 ( select c3a.column_name from user_ind_columns c3a
   where i.index_name=c3a.index_name
 AND rownum<3 )
 AND rownum=1
) column_name_3,
(
 select c4.column_name from user_ind_columns c4
 where  i.index_name=c4.index_name
 AND NOT c4.column_name in
 ( select c4a.column_name from user_ind_columns c4a
   where i.index_name=c4a.index_name
 AND rownum<4 )
 AND rownum=1
) column_name_4
from user_indexes i
;

Have fun !
John David Birch

Jorge <pumuky22@usa.net> wrote in message
news:7umovi$t98$1@diana.bcn.ttd.net...
> 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
>
>