Transpose Row to Column Values in a query 1

[Shantha]

Hi,

Would require help in transposing row values to single column values

The below query provides the list of indexes and the index columns.
In case of composite indexes, multiple index column is appearing in seperate rows.

select a.INDEX_NAME, b. column_name
from all_indexes a, all_ind_columns b
where a.index_name = b.index_name

would like to have query which would display index name and all of the index columns together.
To be more precise,


Index_name:
IDX_XXXX_T1

Column_name:
FLD1
FLD2
FLD3

Result should be

IDX_XXXX_T1.(FLD1,FLD2,FLD3)

Need your help in framing the query.

Thanks & Regards,
Shantha.

Talent is what you possess;
genius is what possesses you

 

[KenCunningham]

Talent is what you possess;
genius is what possesses you

So what has your talent/genius come up with so far?


Some days are diamonds, some days are rocks - make sure most are the former.

 

[SantaMufasa]

Ken,

Although I totally agree with the principle of having a poster disclose the code s/he has already tried in resolving their problem, in Shantha's case, if s/he has never seen the method for producing matrix output in Oracle SQL (version 10g), then I, personally, believe that it is reasonable to disclose a working example of how to achieve matrix results (especially due to the syntactical complexities of 10g matrix output). So, I hope you won't mind my meddling here, by my posting code that achieves what Shantah needs.

Shantha,

The following code relies upon multiple principles that usually appear in "Advanced Oracle SQL" classes:[ul][li]Oracle Analytical functions [e.g., "row_number() over (partition..."][/li][li]sys_connect_by_path...start with...connect by prior[/li][/ul]Once you see the code and the resulting output, it will be your job to investigate and understand the code I've used. Once you do your job (of investigating and understanding), if you still have trouble with the "understanding" portion, then post your question(s) here.

col x heading "Indexes and Their Indexed Columns"
select index_owner||'.'||index_name||'.('
     ||substr(max(sys_connect_by_path(column_name,',')),2)
     ||')' x
  from (select index_owner
              ,index_name
              ,column_name
              ,row_number() over (partition by index_owner,index_name
                                      order by column_position) rn
          from all_ind_columns)
 start with rn=1
connect by prior rn = rn - 1
       and prior index_owner = index_owner
       and prior index_name  = index_name
 group by index_owner, index_name
 order by index_owner, index_name
/

Indexes and Their Indexed Columns
-------------------------------------------
SYS.I_AUDIT_ACTIONS.(ACTION,NAME)
SYS.I_STMT_AUDIT_OPTION_MAP.(OPTION#,NAME)
SYS.I_SYSTEM_PRIVILEGE_MAP.(PRIVILEGE,NAME)
SYS.I_TABLE_PRIVILEGE_MAP.(PRIVILEGE,NAME)
SYSTEM.HELP_TOPIC_SEQ.(TOPIC,SEQ)
TEST.ABCXYZ.(LAST_NAME)
TEST.S_EMP_ID_PK.(ID)
TEST.S_EMP_USERID_UK.(USERID)

Let us know if this does what you hoped for.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[KenCunningham]

Dave - of course I have no problem with your intervention, it's most welcome and I hope it helped Shantha.

Some days are diamonds, some days are rocks - make sure most are the former.