Expert: Elliot Mak - 5/15/2009

Question
Good day Sir!
How do I create a view where the columns are based on data in the table?Is that possible? For example I have an audit table:
a_logno varchar(10)
a_eventid varchar(10)
a_datetime date
a_userid varchar(10)

say the values for a_eventid are A, B, C
and i want to create a view that will look like this:
a_logno varchar(10)
A datetime
B datetime
C datetime
and the contents of columns A,B,C are the datetime corresponding to the events.
Is this possible Sir?
Would really appreciate your help. Thanks very much!

Answer
Good day Vee,

I apologize for the late reply.  It is currently a long weekend here in Canada and I just happened to check my email.

To answer your question, it looks like you want to transpose the row to column.  With Oracle, you can use the following

Here is an example to help you to do that
http://www.dba-oracle.com/t_converting_rows_columns.htm
http://www.oracle.com/technology/oramag/code/tips2004/050304.html

Tom Kyte also had a thread to talk about another way to do that
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:124812348063

Hope it helps.  Let me know you have difficulties in getting this works.

Cheers
Elliot