title: Convert rows into columns Oracle
body: <p>This has been asked several times and has been answered as many times, but this is different because:</p>
  
  <ol>
  <li>this is in Oracle 11g - other examples I have seen are in SQL Server with StUFF function</li>
  <li>This requires joining 2 tables to get the info </li>
  </ol>
  
  <p>Let me explain</p>
  
  <p>This is the table that holds field names &amp; File_ID</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE RESPONSE_METADATA
  (
    FILE_ID        NUMBER(10)                     NOT NULL,
    FIELD_NUMBER   NUMBER(4)                      NOT NULL,
    FIELD_NAME     VARCHAR2(50 BYTE)              NOT NULL,
    FIELD_TYPE     VARCHAR2(50 BYTE)              NOT NULL
  );
  </code></pre>
  
  <p>This is a table that holds the records of each file but records are separated.</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE RESPONSE_DETAILS
  (
    FILE_ID        NUMBER(10),
    REC_FLD_1      VARCHAR2(300 BYTE),
    REC_FLD_2      VARCHAR2(300 BYTE),
    REC_FLD_3      VARCHAR2(300 BYTE),
    REC_FLD_4      VARCHAR2(300 BYTE),
    REC_FLD_5      VARCHAR2(300 BYTE),
    REC_FLD_6      VARCHAR2(300 BYTE),
    REC_FLD_7      VARCHAR2(300 BYTE),
    REC_FLD_8      VARCHAR2(300 BYTE),
    REC_FLD_9      VARCHAR2(300 BYTE),
    REC_FLD_10     VARCHAR2(300 BYTE),
    REC_FLD_11     VARCHAR2(300 BYTE),
    REC_FLD_12     VARCHAR2(300 BYTE),
    REC_FLD_13     VARCHAR2(300 BYTE),
    REC_FLD_14     VARCHAR2(300 BYTE),
    REC_FLD_15     VARCHAR2(300 BYTE),
    REC_FLD_16     VARCHAR2(300 BYTE),
    REC_FLD_17     VARCHAR2(300 BYTE),
    REC_FLD_18     VARCHAR2(300 BYTE),
    REC_FLD_19     VARCHAR2(300 BYTE),
    REC_FLD_20     VARCHAR2(300 BYTE)
  );
  </code></pre>
  
  <p>Insert Data</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,1,'REC_ID','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,2,'EMAIL','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,3,'CAMP','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,4,'ACTION','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,5,'DESC','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,6,'TIME','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,7,'CHANNEL','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,8,'STATUS','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,9,'DI_CODE','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (1,10,'CUSTOM','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,1,'REC_ID','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,2,'EMAIL','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,3,'CAMP','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,4,'ACTION','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (2,5,'DESC','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,1,'REC_ID','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,2,'EMAIL','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (3,3,'CAMP','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,1,'REC_ID','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,2,'ORGN','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,3,'LOGOS','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,4,'ACTN','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,5,'BLANK1','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,6,'ALERT_ID','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,7,'DEVNUM','NUMBER');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,8,'RES_CODE','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,9,'STRAT_ID','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,10,'COMP_FLAG','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,11,'AUDLOG','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,12,'STARTTIME','VARCHAR2');
  INSERT INTO RESPONSE_METADATA (FILE_ID, FIELD_NUMBER, FIELD_NAME, FIELD_TYPE) VALUES (4,13,'DURATION','VARCHAR2');
  
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67537','Line','','81433','1','15062012','DELIVERED','67537','','','','','','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67551','Line','','81430','1','15062012','DELIVERED','67551','','','','','','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67579','Line','','81403','1','15062012','DELIVERED','67579','','','','','','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67593','Line','','81452','1','15062012','DELIVERED','67593','','','','','','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (8,'67607','Line','','81453','1','15062012','DELIVERED','67607','','','','','','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28072','16','1','189325','','ins/code.com/20120621','6551','WID ','VOLN ','N',';B10100','41061.6740856481','14','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28072','16','1','189325002','','ins/code.com/20120622','6551','CON ','VOLN ','N',';B10100','41061.6978240741','124','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28075','15','30','443433001','','ins/code.com/20120623','5908','AMP ','VOLN ','N',';B10100','41061.6737268519','13','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28075','15','30','4443433','','ins/code.com/20120624','5908','CON ','VOLN ','N',';B10100','41061.6953240741','113','','','','','','','');
  INSERT INTO RESPONSE_DETAILS (FILE_ID, REC_FLD_1, REC_FLD_2, REC_FLD_3, REC_FLD_4, REC_FLD_5, REC_FLD_6, REC_FLD_7, REC_FLD_8, REC_FLD_9, REC_FLD_10, REC_FLD_11, REC_FLD_12, REC_FLD_13, REC_FLD_14, REC_FLD_15, REC_FLD_16, REC_FLD_17, REC_FLD_18, REC_FLD_19, REC_FLD_20)VALUES (4,'28078','11','1','2576141','','ins/code.com/20120625','6681','CON ','VOLN ','N',';B10100','41061.6764236111','81','','','','','','','');
  </code></pre>
  
  <p>Now the Problem IS</p>
  
  <p>I want data to be seen this way</p>
  
  <pre class="lang-sql prettyprint-override"><code>FILE_ID REC_ID  EMAIL   CAMP    ACTION  DESC    TIME    CHANNEL STATUS  DI_CODE CUSTOM
  1   67537   Line        81433   1   15062012    DELIVERED   67537       
  1   67551   Line        81430   1   15062012    DELIVERED   67551       
  1   67579   Line        81403   1   15062012    DELIVERED   67579       
  1   67593   Line        81452   1   15062012    DELIVERED   67593       
  1   67607   Line        81453   1   15062012    DELIVERED   67607       
  
  
  FILE_ID REC_ID  ORGN    LOGOS   ACTN    BLANK1  ALERT_ID            DEVNUM  RES_CODE    STRAT_ID    COMP_FLAG   AUDLOG  STARTTIME   DURATION
  4   28072   16  1   189325          ins/code.com/20120621   6551    WID         VOLN    N       ;B10100 1/06/2012 16:10 14
  4   28072   16  1   1893250         ins/code.com/20120622   6551    CON         VOLN    N       ;B10100 1/06/2012 16:44 124
  4   28075   15  30  4434330         ins/code.com/20120623   5908    AMP         VOLN    N       ;B10100 1/06/2012 16:10 13
  4   28075   15  30  4443433         ins/code.com/20120624   5908    CON         VOLN    N       ;B10100 1/06/2012 16:41 113
  4   28078   11  1   2576141         ins/code.com/20120625   6681    CON         VOLN    N       ;B10100 1/06/2012 16:14 81
  </code></pre>
  
  <p>The way it needs to work is every File_ID in METADATA Table has FIELD_NUMBER. These Filed_Numbers are columns in Details table </p>
  
  <p>I've tried XML PIVOT in Oracle that does not work as there are multiple nodes these columns will create.</p>
  
  <p>I've tried just pivot things - does not work. THIS NEEDS TO BE DYNAMIC - THE File_ID can have any number of Fields not more than 30 though.</p>
  
  <p>And this needs to be a stored proc or query - as I do not have permission to be build ETL.</p>