CODEWITHSUNDEEP
sqloracle11gpivotunpivot
Tony Wolff
Tony Wolff

Reputation: 742

Transforming a table ? using pivot / unpivot

My brain is hurting! I want to transform the data in the table generated from the below sql. The examples of the use of pivot tends to involve summarizing the data. Clearly here I am actually expanding the data.

CREATE TABLE GCS 
(
  EYES VARCHAR2(20) 
, VERBAL VARCHAR2(20) 
, MOTOR VARCHAR2(20) 
, UNITNUM VARCHAR2(20) 
);

Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140560');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('3',4,'2','140729');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('1',2,'6','140771');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140502');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',4,'6','140537');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140566');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140571');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140781');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140780');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('3',4,'5','140788');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140585');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140577');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140747');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140778');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140569');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',1,'6','140575');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140779');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140785');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140753');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140786');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140555');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140557');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140554');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140736');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',4,'6','140745');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140783');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140556');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140559');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140574');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140573');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140572');
Insert into GCS ("Eyes","Verbal","Motor",UNITNUM) values ('4',5,'6','140738');
commit;

into the following form. I am sure I need to use pivot / unpivot but can't work out how.

Item  | Score | Unitnum
----------
Eyes    4   140560
Verbal  5   140560
Motor   6   140560
Eyes    3   140729
Verbal  4   140729
Motor   2   140729
Eyes    1   140771
Verbal  2   140771
Motor   6   140771

.....etc

Upvotes: 0

Views: 59

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

Simple way is by using Unpivot operator

select item,
       score,
       unitnum,
from   gcs
       unpivot (score
               for item in(eyes,
                           verbal,
                           motor))

SQLFIDDLE DEMO

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116140

You can use a simple union to get these values:

select 'Eyes' as Item, Eyes, UnitNum
from GCS
union all
select 'Verbal' as Item, Verbal, UnitNum
from GCS
union all
select 'Motor' as Item, Motor, UnitNum
from GCS

And you can order by those columns if you need, including the just introduced 'Item':

select 'Eyes' as Item, Eyes, UnitNum
from GCS
union all
select 'Verbal' as Item, Verbal, UnitNum
from GCS
union all
select 'Motor' as Item, Motor, UnitNum
from GCS
order by
  unitnum, item

See SQLFiddle

Upvotes: 1

Related Questions