CODEWITHSUNDEEP
oracle-databasepivot-table
Sandeep Ranjan
Sandeep Ranjan

Reputation: 834

ORA-00936: missing expression while using pivot

I need to pivot the table. Following are my queries.

CREATE TABLE SurveyResponses (
    id int,
    question varchar(255),
    answer varchar(255)
);

INSERT INTO SurveyResponses (id, question, answer)  values (1, 'question 1', 'responseX');
INSERT INTO SurveyResponses (id, question, answer)  values (2, 'question 1', 'responseY');
INSERT INTO SurveyResponses (id, question, answer)  values (3, 'question 1', 'responseZ');
INSERT INTO SurveyResponses (id, question, answer)  values (4, 'question 2', 'responseA');
INSERT INTO SurveyResponses (id, question, answer)  values (5, 'question 2', 'responseB');
INSERT INTO SurveyResponses (id, question, answer)  values (6, 'question 3', 'responseC');

When I try to use pivot the table with the question as columns I get

ORA-00936: missing expression

What is that I'm missing in my query? I want it to be dynamic because I have 500+ question in the real table.

Query

SELECT * FROM (
    SELECT id, question, answer from SurveyResponses
) SurveyResponsesResults
PIVOT (
    MAX(answer)
    FOR question
    In(
        SELECT DISTINCT question FROM SurveyResponses
    )
) As pivottable;

Upvotes: 0

Views: 326

Answers (2)

Sandeep Ranjan
Sandeep Ranjan

Reputation: 834

Extending @Littlefoot answer.

If I have to use dynamic input in IN.

I will have to use PIVOT XML with xmlserialize the query is as follows.

SELECT xmlserialize(content pivottable.question_XML) from (
    SELECT id, question, answer from SurveyResponses
) SurveyResponsesResults
PIVOT XML (
    Max(answer)
    FOR question
    In(
        SELECT DISTINCT question FROM SurveyResponses
    )
) pivottable ;

The query works but it outputs in XML format.

I'm trying to figure out how to convert the XML to TABLE. If you know feel free to edit.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142958

IN can't be dynamic. Use line #7 instead of (commented) line #8.

SQL> select * from (
  2      select id, question, answer from SurveyResponses
  3  ) SurveyResponsesResults
  4  PIVOT (
  5      max(answer)
  6      For question
  7      In('question 1' as q1, 'question 2' as q2, 'question 3' as q3
  8          --SELECT DISTINCT question FROM SurveyResponses
  9      )
 10  ) ;

        ID Q1         Q2         Q3
---------- ---------- ---------- ----------
         1 responseX
         6                       responseC
         2 responseY
         4            responseA
         5            responseB
         3 responseZ

6 rows selected.

SQL>

Upvotes: 2

Related Questions