Search code examples
sqloracle11gpivotunpivot

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

Solution

  • Simple way is by using Unpivot operator

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

    SQLFIDDLE DEMO