CODEWITHSUNDEEP
sqloracle-database
Mohan
Mohan

Reputation: 153

Transpose a table in Oracle

The original table looks like below

<h2>Original table:</h2>
<table border="1" style="width:100%" cellpadding="0" cellspacing="0">
<tr style="background-color:#aaa"><th>S.No</th><th>Name</th><th>No1</th><th>No2</th></tr>
<tr><th>1</th><th>Test1</th><th>10</th><th>50</th></tr>
<tr><th>2</th><th>Test2</th><th>20</th><th>30</th></tr>
<tr><th>3</th><th>Test3</th><th>40</th><th>20</th></tr>
<tr><th>4</th><th>Test4</th><th>80</th><th>90</th></tr>
</table>

I want to transpose the above table into below table. 

<h2>Output:</h2>
<table border="1" style="width:100%" cellpadding="0" cellspacing="0">
<tr style="background-color:#aaa"><th>S.No</th><th>Test1</th><th>Test2</th><th>Test3</th><th>Test4</th></tr>
<tr><th>1</th><th>10</th><th>20</th><th>40</th><th>80</th></tr>
<tr><th>2</th><th>50</th><th>30</th><th>20</th><th>90</th></tr>
</table>

Upvotes: 1

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You want to unpivot and re-pivot. Here is one method:

select s_no,
       max(case when name = 'test1' then num else 0 end) as test1,
       max(case when name = 'test2' then num else 0 end) as test2,
       max(case when name = 'test3' then num else 0 end) as test3,
       max(case when name = 'test4' then num else 0 end) as test4
from ((select name, 'no1' as s_no, no1 as num from t
      ) union all
      (select name, 'no2', no2 from t
      )
     ) nn
group by s_no
order by s_no;

Upvotes: 1

Related Questions