title: Oracle 12c Pivoting data rows to columns
body: <p>I have an Oracle 12c Pivot Question.</p>
  
  <p>I need to create a row of data that shows the changes made by a customer to his profile at a given time so that I can move my date to a warehouse structure.  The data is normalized into the tables Investor (INV), Phone(PHH), Email(EMH). </p>
  
  <p>I’ve created a query that gives me the very basics, which investor, what changed, when, and a pointer to the information that I need with one row for each table change that has occurred. I would like to pivot this data into a single row and then use those rows to go get the additional data.  </p>
  
  <p>I am getting ORA-56901 non-constant expression is not allowed when I try to run my pivot query.  I thought that I was using the constants in the TABLE_ALIAS column.  This is my first attempt at trying to use this type of syntax. I've used examples from other questions about pivoting posted here and on other sites to help me construct my query. Can someone please help me to understand where I’ve gone wrong? Thanks!</p>
  
  <p>A representation of the results of my query is this:</p>
  
  <pre><code>  CREATE   TABLE "DBO"."CHANGES"
    (
      "MY_ROW_ID"        NUMBER NOT NULL ENABLE,
      "WHEN_IT_HAPPENED" TIMESTAMP (6) NOT NULL ENABLE,
      "INVESTOR_ID"      NUMBER NOT NULL ENABLE,
      "HISTORY_ID"       NUMBER NOT NULL ENABLE,
      "TABLE_ALIAS"      VARCHAR2(7 BYTE) NOT NULL ENABLE,
      CONSTRAINT "CHANGES_PK" PRIMARY KEY ("MY_ROW_ID") USING INDEX PCTFREE 10
      INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE "USERS" ENABLE
    )  ;
  </code></pre>
  
  <p>Adding the data:</p>
  
  <pre><code>Insert into DBO.CHANGES values (1,to_timestamp('08-MAR-2017 11.47.46.717417000 AM','DD-MON-YYYY HH.MI.SSXFF AM'),2158,2186,'IVH');
  Insert into DBO.CHANGES values (2,to_timestamp('08-MAR-2017 11.47.46.717417000 AM','DD-MON-YYYY HH.MI.SSXFF AM'),2158,2508,'PHH2');
  Insert into DBO.CHANGES values (3,to_timestamp('08-MAR-2017 11.46.25.284464000 AM','DD-MON-YYYY HH.MI.SSXFF AM'),2158,2507,'EMH');
  Insert into DBO.CHANGES values (4,to_timestamp('08-MAR-2017 10.28.13.366506000 AM','DD-MON-YYYY HH.MI.SSXFF AM'),1004,2490,'EMH');
  Insert into DBO.CHANGES values (5,to_timestamp('08-MAR-2017 10.33.46.101412000 AM','DD-MON-YYYY HH.MI.SSXFF AM'),1004,2153,'IVH');
  Insert into DBO.CHANGES values (6,to_timestamp('08-MAR-2017 12.11.58.539055000 PM','DD-MON-YYYY HH.MI.SSXFF AM'),1004,2166,'IVH');
  Insert into DBO.CHANGES values (7,to_timestamp('08-MAR-2017 12.11.58.539055000 PM','DD-MON-YYYY HH.MI.SSXFF AM'),1004,2046,'PHH1');
  </code></pre>
  
  <p>So this is some of my data for a couple of investors:</p>
  
  <pre><code>MY_ROW_ID   WHEN_IT_HAPPENED    INVESTOR_ID HISTORY_ID TABLE_ALIAS
  1   08-MAR-2017 11.47.46.717417000  2158        2186        IVH
  2   08-MAR-2017 11.47.46.717417000  2158        2508        PHH2
  3   08-MAR-2017 11.46.25.284464000  2158        2507        EMH
  4   08-MAR-2017 10.28.13.366506000  1004        2490        EMH 
  5   08-MAR-2017 10.33.46.101412000  1004        2153        IVH
  6   08-MAR-2017 12.11.58.539055000  1004        2166        IVH
  7   08-MAR-2017 12.11.58.539055000  1004        2046        PHH1
  </code></pre>
  
  <p>What I want to see is this:</p>
  
  <pre><code>INVESTOR_ID  WHEN_IT_HAPPENED           IVH     PHH1    PHH2    EMH
  1004    08-MAR-2017 10.28.13.366506000                          2490
  1004    08-MAR-2017 10.33.46.101412000  2153
  1004    08-MAR-2017 12.11.58.539055000  2166    2046
  2158    08-MAR-2017 11.47.46.717417000  2186            2508
  2158    08-MAR-2017 11.46.25.284464000                          2507
  </code></pre>
  
  <p>Here is what I tried:</p>
  
  <pre><code>SELECT    INVESTOR_ID
          , WHEN_IT_HAPPENED
          , IVH
          , PHH1
          , PHH2
          , EMH
  FROM      ( SELECT    INVESTOR_ID
                      , WHEN_IT_HAPPENED
                      , TABLE_ALIAS
                      , HISTORY_ID
              FROM      CHANGES
             )
  PIVOT
  (
    MAX (HISTORY_ID)
    FOR  TABLE_ALIAS
    IN  ( IVH, PHH1, PHH2, EMH )
   )   ;
  </code></pre>
  
  <p>ORA-56901: non-constant expression is not allowed for pivot|unpivot values
  56901. 0000 -  "non-constant expression is not allowed for pivot|unpivot values"
  *Cause:    Attempted to use non-constant expression for pivot|unpivot values.
  *Action:   Use constants for pivot|unpivot values.
  Error at Line: 17 Column: 9  (This is my IN clause for the pivot)</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks so much for the help.  I'm sure there may be other approaches but, the following is what worked for me.  With this new tool in my bag, I can now create my query to get all of the data needed.</p>
  
  <pre><code>SELECT    INVESTOR_ID
          , WHEN_IT_HAPPENED
          , IVH_HIST_ID
          , PHH1_HIST_ID
          , PHH2_HIST_ID
          , PHH3_HIST_ID
  --SELECT      *  -- this works too.  I just prefer to list things for clarity.
  FROM      ( SELECT    INVESTOR_ID
                      , WHEN_IT_HAPPENED
                      , TABLE_ALIAS
                      , HISTORY_ID
              FROM      CHANGES
             )
  PIVOT
  (
    MIN (HISTORY_ID)  AS HIST_ID
    FOR  TABLE_ALIAS 
    IN  (   'IVH'   AS  IVH
          , 'PHH1'  AS  PHH1
          , 'PHH2'  AS  PHH2
          , 'PHH3'  AS  PHH3
         )
   )  
   ORDER BY INVESTOR_ID
          , WHEN_IT_HAPPENED
   ;
  </code></pre>