I have a data like below in a table
create table test (
sr varchar2(1),col1 number,col2 number,col3 number
) ;
insert into test values ('a',1,2,3);
insert into test values ('b',4,5,6);
insert into test values ('c',7,8,9);
Want out put like:
Col A B C
Col1 1 4 7
Col2. 2 5 8
Col2 3 6 9
Please help
So you want to flip the rows and columns around, aka a transpose?
You can do this by chaining unpivot and pivot operations.
First an unpivot, to turn the column values into rows:
select * from test
unpivot (
val for col in ( col1, col2, col3 )
);
SR COL VAL
a COL1 1
a COL2 2
a COL3 3
b COL1 4
b COL2 5
b COL3 6
c COL1 7
c COL2 8
c COL3 9
Then add a pivot afterwards to create a column for each value for SR:
select * from test
unpivot (
val for col in ( col1, col2, col3 )
)
pivot (
min ( val ) for sr in (
'a' A, 'b' B, 'c' C
)
);
COL A B C
COL1 1 4 7
COL2 2 5 8
COL3 3 6 9
Read more about this at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#transpose