Oracle SQL: Pivot on multiple columns/fields
<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>