Skip to Main Content
  • Questions
  • Converting Row to Column and Vice-versa

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajesh.

Asked: July 19, 2016 - 2:53 pm UTC

Last updated: July 19, 2016 - 4:01 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

I have a table Assigned_Task.

ENAME JANUARY FEBRUARY MARCH APRIL
------ -------- -------- ------ ------
RASHMI 20 25 30 05
SOUMYA 11 21 09 15
BISWA 17 33 19 00


I want the output as below format:

MONTH RASHMI SOUMYA BISWA
----- ------- -------- -------
JANUARY 20 11 17
FEBRUARY 25 21 33
MARCH 30 09 19
APRIL 05 15 00

Please help me.

Regards,
Rajesh

and Chris said...

If you're looking transpose the rows and columns, you can unpivot and pivot back again:

CREATE TABLE t
    (ENAME varchar(6), JANUARY int, FEBRUARY int, MARCH int, APRIL int)
;
    
INSERT INTO t VALUES  ('RASHMI', 20, 25, 30, 05);
INSERT INTO t VALUES ('SOUMYA', 11, 21, 09, 15);
INSERT INTO t VALUES ('BISWA', 17, 33, 19, 00);

select * from t
unpivot  (
  val for mth in (january as 'jan', february as 'feb', march as 'mar', april as 'apr')
);

ENAME   MTH  VAL  
RASHMI  jan  20   
RASHMI  feb  25   
RASHMI  mar  30   
RASHMI  apr  5    
SOUMYA  jan  11   
SOUMYA  feb  21   
SOUMYA  mar  9    
SOUMYA  apr  15   
BISWA   jan  17   
BISWA   feb  33   
BISWA   mar  19   
BISWA   apr  0

select * from (
  select * from t
  unpivot  (
    val for mth in (january as 'jan', february as 'feb', march as 'mar', april as 'apr')
  )
)
pivot (
  sum(val) as v for (ename) in ('RASHMI' as RASHMI, 'SOUMYA' as SOUMYA, 'BISWA' as BISWA)
);

MTH  RASHMI_V  SOUMYA_V  BISWA_V  
jan  20        11        17       
apr  5         15        0        
feb  25        21        33       
mar  30        9         19 

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Rajesh Kumar Sethi, July 20, 2016 - 10:28 am UTC

Thank You Chris. It is very much useful for me.