I am working at a client, we would like to convert information from multiple rows to a single row with multiple columns. I know we can do this using PL/SQL procedure, i was wondering if there is a sophisticated SQL to get this result.
Here is explanation of the problem. We have 2 tables called ticket and old_ticket. Both tables have following fields Ticket#, Document# which are PK's. A particular Ticket# can have multiple documents in each table, i would like create a view or a table to have only one row per ticket with all the document#s as columns in the row. The number of documents might change for each ticket.
Row1 1 1
Row2 1 2
Row3 1 4
Row4 2 1
Row5 2 3
Row1 1 3
Row2 1 5
Row3 2 2
I would like my resulting table or view to give me following results.
Ticket# doc1 doc2 doc3 doc4 doc5
Row1 1 1 2 3 4 5
Row2 2 1 2 3
Any thoughts regards to this would be of great help.
Click Here to Expand Forum to Full Width