rob@XE:11g2> CREATE TABLE nfl2012_scores AS

  2  SELECT

  3    team,

  4    SUBSTR(division,1,4) AS div,

  5    game,

  6    opponent AS opp,

  7    DECODE(SUBSTR(opponent,1,1), '@','AWAY', 'HOME') AS venue,

  8    ptsf,

  9    ptsa,

 10    (CASE

 11       WHEN ptsf > ptsa THEN 'WIN'

 12       WHEN ptsf < ptsa THEN 'LOSS'

 13       WHEN ptsf = ptsa THEN 'TIE'

 14     END) wlt

 15  FROM

 16  (

 17    SELECT *

 18    FROM ext_nfl2012_scores

 19    UNPIVOT

 20    (

 21      (opponent, ptsf, ptsa) FOR game IN

 22      (

 23        (opponent1, points_for1, points_against1) AS 'wk1',

 24        (opponent2, points_for2, points_against2) AS 'wk2',

 25        (opponent3, points_for3, points_against3) AS 'wk3',

 26        (opponent4, points_for4, points_against4) AS 'wk4',

 27        (opponent5, points_for5, points_against5) AS 'wk5',

 28        (opponent6, points_for6, points_against6) AS 'wk6',

 29        (opponent7, points_for7, points_against7) AS 'wk7',

 30        (opponent8, points_for8, points_against8) AS 'wk8',

 31        (opponent9, points_for9, points_against9) AS 'wk9',

 32        (opponent10, points_for10, points_against10) AS 'wk10',

 33        (opponent11, points_for11, points_against11) AS 'wk11',

 34        (opponent12, points_for12, points_against12) AS 'wk12',

 35        (opponent13, points_for13, points_against13) AS 'wk13',

 36        (opponent14, points_for14, points_against14) AS 'wk14',

 37        (opponent15, points_for15, points_against15) AS 'wk15',

 38        (opponent16, points_for16, points_against16) AS 'wk16'

 39      )

 40    )

 41  );

Table created.

rob@XE:11g2> select count(*) from nfl2012_scores;

  COUNT(*)

==========

       512

rob@XE:11g2> select * from nfl2012_scores where rownum < 35;

TEAM DIV    GAME OPP  VENUE     PTSF     PTSA WLT

==== ====== ==== ==== ===== ======== ======== ====

NYG  NFCE   wk1  DAL  HOME        17       24 LOSS

NYG  NFCE   wk2  TB   HOME        41       34 WIN

NYG  NFCE   wk3  @CAR AWAY        36        7 WIN

NYG  NFCE   wk4  @PHI AWAY        17       19 LOSS

NYG  NFCE   wk5  CLE  HOME        41       27 WIN

NYG  NFCE   wk6  @SF  AWAY        26        3 WIN

NYG  NFCE   wk7  WAS  HOME        27       23 WIN

NYG  NFCE   wk8  @DAL AWAY        29       24 WIN

NYG  NFCE   wk9  PIT  HOME        20       24 LOSS

NYG  NFCE   wk10 @CIN AWAY        13       31 LOSS

NYG  NFCE   wk11 GB   HOME        38       10 WIN

NYG  NFCE   wk12 @WAS AWAY        16       17 LOSS

NYG  NFCE   wk13 NO   HOME        52       27 WIN

NYG  NFCE   wk14 @ATL AWAY         0       34 LOSS

NYG  NFCE   wk15 @BAL AWAY        14       33 LOSS

NYG  NFCE   wk16 PHI  HOME        42        7 WIN

SF   NFCW   wk1  @GB  AWAY        30       22 WIN

SF   NFCW   wk2  DET  HOME        27       19 WIN

SF   NFCW   wk3  @MIN AWAY        13       24 LOSS

SF   NFCW   wk4  @NYJ AWAY        34        0 WIN

SF   NFCW   wk5  BUF  HOME        45        3 WIN

SF   NFCW   wk6  NYG  HOME         3       26 LOSS

SF   NFCW   wk7  SEA  HOME        13        6 WIN

SF   NFCW   wk8  @AZ  AWAY        24        3 WIN

SF   NFCW   wk9  STL  HOME        24       24 TIE

SF   NFCW   wk10 CHI  HOME        32        7 WIN

SF   NFCW   wk11 @NO  AWAY        31       21 WIN

SF   NFCW   wk12 @STL AWAY        13       16 LOSS

SF   NFCW   wk13 MIA  HOME        27       13 WIN

SF   NFCW   wk14 @NE  AWAY        41       34 WIN

SF   NFCW   wk15 @SEA AWAY        13       42 LOSS

SF   NFCW   wk16 AZ   HOME        27       13 WIN

SEA  NFCW   wk1  @AZ  AWAY        16       20 LOSS

SEA  NFCW   wk2  DAL  HOME        27        7 WIN

34 rows selected.