Rows as Columns and Vice Versa using Oracle SQL

Question

I have the data coming from an SQL in the below format as per screenshot 1 and I would need in the format as per the screenshot 2.

Please note the data that is in Screenshot 1 is dynamic. There can be any number of Rows and any number of columns. Also, I need this to be in a single SQL Query and not a PL/SQL Procedure.

Current SQL Output:

Desired SQL Output:

Can someone please shed some light on this?

Thanks, Abhishek.

1. [**Do not post images of data**](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question#285557), they are not useful. 2. Did you try to [search](https://stackoverflow.com/search?q=rows+to+columns+%5Boracle%5D)? Also there's no *vice versa* actually, because you just transform rows to columns. I cannot see any column became a row, do you? — astentx, Jul 21 '21 at 08:51

Meysam Asadi · Answer 1 · 2021-07-21T22:19:47.393

0

With pivot, the desired output is easily obtained

select * 
from myTable
pivot (
    max (COLUMN_NAME_VALUE)
    for COLUMN_NAME
    in (
       'COL1' as COL1,'COL2' as COL2,'COL3' as COL3
   )
)
ORDER BY ROW_NAME;

demo in db<>fiddle

edited Jul 21 '21 at 22:19

answered Jul 21 '21 at 10:58

Meysam Asadi

6,438
3
7
17

1

Thats not flexible enough, as the per the question the data is dynamic and there can be any number of rows/columns. – Thomas Tschernich Jul 21 '21 at 12:19

Rows as Columns and Vice Versa using Oracle SQL

1 Answers1