CODEWITHSUNDEEP
sqloracle-databasestring-concatenation
Bala S
Bala S

Reputation: 523

Data transpose using Oracle SQL query

I'm having table and data like below, 

Create table transpose (a number, b number, c number); 

insert into transpose values (1,4,7);
insert into transpose values (2,5,8); 
insert into transpose values (3,6,9); 
commit; 
select * from transpose; 

A B C 
1 4 7 
2 5 8 
3 6 9

Question: I need below output using sql query, Is this possbile to retrive data (Transpose data)? 

   A B C 
   1 2 3 
   4 5 6 
   7 8 9

Please help to resolve this.

Upvotes: 1

Views: 320

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

There are two important things to consider.

1) a SQL table has no implicite order, so you must add the order by information to uniquely define the columns of the transposed table. (I added a column ID in the table to simulate it).

2) If you are looking for a solution for fixed number of columns and rows, you may code the result in the SQL query see below (For a dynamic solution I'd not recommend to use SQL)

 select 
   (select a from transpose where id = 1) a,
   (select a from transpose where id = 2) b,
   (select a from transpose where id = 3) c
 from dual
 union all
 select 
   (select b from transpose where id = 1) a,
   (select b from transpose where id = 2) b,
   (select b from transpose where id = 3) c
 from dual
 union all
 select 
   (select c from transpose where id = 1) a,
   (select c from transpose where id = 2) b,
   (select c from transpose where id = 3) c
 from dual;

.

     A          B          C
 ---------- ---------- ----------
     1          2          3 
     4          5          6 
     7          8          9

UPDATE - solution with PIVOT

As proposed by @WW here solution with pivot

 with trans as (
 /* add one select for each column */
 select id, 'A' col_name, a col_value from transpose union all
 select id, 'B' col, b col_value from transpose union all
 select id, 'C' col, b col_value from transpose)
 select * from trans
 PIVOT (sum(col_value) col_value 
 for (id) in 
 (1 as "C1", /* add one entry for each row (ID) */
 2 as "C2",
 3 as "C3" )
 )

Upvotes: 0

Related Questions