How to transpose columns to rows in below scenario.
I have a scenario in which i need to transpose the columns to the rows. select se.time, e.description,se.created_by from status se, status_desc e where
se.id and se.shipment_id=12550060 order by time ; O/P: Timestamp Status Owner 11:08 A s 11:08 B s 12:37 C s 14:38 C s 14:55 C s 14:55 C s 17:31 D A 11:34 E A I need to show this like: Time 1 Status 1 Owner 1 Time 2 Status 2 Owner 2 Time 3 Status 3 Owner 3 Time 4 Status 4 Owner 4 Time 5 Status 5 Owner 5 Time 6 Status 6 Owner 6 Time 7 Status 7 Owner 7 Time 8 Status 8 Owner 8
The tags suggest that this is a question about Oracle. This makes it a bit easier to transform the data because in T-SQL there is a limitation of using only one column to pivot while in Oracle's PL-SQL it is possible to use multiple. In this case, if the number of rows for each **shipment\_id** is known, the static query may be used. The idea is to number the rows partitioning by **shipment\_id** so that if the source query needs to include more than one **shipment\_id**, the result will be one row per such ID with 24 columns (8 rows times 3 original columns). Something like this should work: select * from ( select se.shipment_id, se.timestamp, e.description, se.created_by, row_number() over (partition by se.shipment_id order by se.timestamp) N from status se, status_desc e where
se.id and se.shipment_id=12550060 ) pivot ( max(timestamp), max(description), max(created_by) for N in (1, 2, 3, 4, 5, 6, 7, 8) ) Assuming that Oracle version is high enough (10 or better), the above query should work (I just don't have a way to check because I don't work with Oracle data at this time). Hope this helps. Oleg