title: Oracle SQL: Pivot on multiple columns/fields
body: <p>I want to create a table where the entries of various columns are 'pivoted' to column headers. The table is for reporting purposes - my users want to query data via Excel (using Microsoft Query), and the problem is that doing the pivoting in Excel makes the files unpractically large and slow even for moderately-sized data sets (~100k data points).</p>
  
  <p>Consider the following example:</p>
  
  <pre><code>CREATE TABLE tt
  (   
    "COMMODITY" VARCHAR2(4000 BYTE), 
    "MARKET"    VARCHAR2(4000 BYTE), 
    "BID_ASK"   VARCHAR2(4000 BYTE), 
    "PRICE"     NUMBER
  );
  
  INSERT INTO tt VALUES ('Gold','US','Ask',1.1);
  INSERT INTO tt VALUES ('Gold','US','Bid',1);
  INSERT INTO tt VALUES ('Gold','EU','Ask',1.2);
  INSERT INTO tt VALUES ('Gold','EU','Bid',1.1);
  INSERT INTO tt VALUES ('Oil','US','Ask',11);
  INSERT INTO tt VALUES ('Oil','US','Bid',10);
  INSERT INTO tt VALUES ('Oil','EU','Ask',12);
  INSERT INTO tt VALUES ('Oil','EU','Bid',11);
  </code></pre>
  
  <p>The output that I want to achieve would be something like (the exact column headers don't matter much):</p>
  
  <pre><code>COMMODITY   'US_Bid'    'US_Ask'    'EU_Bid'    'EU_Ask'
  Gold         1           1.1         1.1        1.2
  Oil          10          11          11         12
  </code></pre>
  
  <p>Now it is straightforward to pivot a single column:</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT * FROM tt
  )
  PIVOT
  (
    SUM(PRICE)
    FOR MARKET IN ('US','EU')
  )
  </code></pre>
  
  <p>Which gives:</p>
  
  <pre><code>COMMODITY   BID_ASK 'US'    'EU'
  Gold        Bid      1      1.1
  Oil         Bid      10     11
  Oil         Ask      11     12
  Gold        Ask      1.1    1.2
  </code></pre>
  
  <p>According to my research there is no syntax for directly pivoting multiple columns. There are some related questions (<a href="https://stackoverflow.com/questions/19590799/tsql-pivot-multiple-columns">here</a>, <a href="https://stackoverflow.com/questions/18662012/sql-server-pivot-on-multiple-columns">here</a> or <a href="https://stackoverflow.com/questions/8058912/sql-pivot-on-three-colums">here</a>), but I could not find a direct answer to my problem there. So I came up with the following solution:</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT COMMODITY, CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK, PRICE FROM tt
  )
  PIVOT
  (
    SUM(PRICE)
    FOR MARKET_BID_ASK IN ('US_Bid','US_Ask','EU_Bid','EU_Ask')
  )
  </code></pre>
  
  <p>This produces exactly the desired output. However, I do not consider it a practical solution as the number of variables that I have to enter grows way too fast (in my real data set, I want to pivot more fields at once, all of which have many different values). I know that there exist <a href="https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query">dynamic pivots</a>, however I am not sure whether this will work with Excel, and I would also like to keep the syntax as simple as possible, because the users will define queries on their own (I just want to provide a template query that they can adapt). So I tried to query the field names in the IN-clause:</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT COMMODITY, CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK, PRICE FROM tt
  )
  PIVOT
  (
    SUM(PRICE)
    FOR MARKET_BID_ASK IN 
    (
      SELECT DISTINCT CONCAT(CONCAT(MARKET,'_'),BID_ASK) AS MARKET_BID_ASK FROM tt
    )
  )
  </code></pre>
  
  <p>I think that such a solution could be practical, because one could still constrain the variables queried without having to list all the concatenated options using LIKE-conditions in the subquery. However, I get an "ORA-00936 - missing expression" error with this query, even though subqueries should be legal here according to the <a href="http://www.techonthenet.com/oracle/pivot.php" rel="nofollow noreferrer">documentation</a> that I found.</p>