title: pivot does not work when using a nested select in SQL
body: <p>I have a requirement to pivot the columns of a particular table specified from a user.
   the problem is the number of columns from each table to pivot is dynamic.
  so the code below gets the name of the columns from the table.</p>
  
  <pre><code>SELECT DISTINCT 
                  LISTAGG('''' || column_name || '''', ',') 
                      WITHIN GROUP (ORDER BY column_name) AS temp_in_statement 
                  FROM (SELECT DISTINCT column_name FROM all_tab_columns WHERE table_name = 'DIM_XYZ')
  </code></pre>
  
  <p>the code above returns the columns in the following format: </p>
  
  <pre><code>col1, col2
  </code></pre>
  
  <p>I have to use a pivot for this requirement and plug the code above in the code below in order to pivot the columns.</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT table_name, column_name
    FROM ALL_TAB_COLUMNS
    WHERE
        table_name = 'DIM_XYZ'
  
  )
  PIVOT
  (
    MIN(column_name)
    FOR column_name IN ( 
  
  -- values added manually 
  
   'col1','col2'
  
  -- values added manually 
  
  
      )
  )
  ORDER BY table_name;
  </code></pre>
  
  <p>The code works fine in this case but when replacing  'col1','col2' by the select statement to retrieve columns names the system throws the following error: </p>
  
  <pre class="lang-none prettyprint-override"><code>ORA-00936: missing expression
  00936. 00000 -  "missing expression"
  *Cause:    
  *Action:
  Error at Line: 39 Column: 40
  </code></pre>
  
  <p>CODE:</p>
  
  <pre><code>SELECT * FROM
  (
    SELECT table_name, column_name
    FROM ALL_TAB_COLUMNS
    WHERE
        table_name = 'DIM_XYZ'
  
  )
  PIVOT
  (
    MIN(column_name)
    FOR column_name IN ( 
  
  --code below does not work when plugged in the statement above
                SELECT DISTINCT 
                  LISTAGG('''' || column_name || '''', ',') 
                      WITHIN GROUP (ORDER BY column_name) AS temp_in_statement 
                  FROM (SELECT DISTINCT column_name FROM all_tab_columns WHERE table_name = 'DIM_XYZ')
   --code above does not work
      )
  )
  ORDER BY table_name;
  ----------------
  </code></pre>
  
  <p>do you guys have any idea how to solve this problem?</p>