Pivot/Transpose in SQL Query in Oracle

Question

I didn't know if my problem is related to Pivoting or Transposing so that's why I wrote both in the title.

Below is my query (using it in an Oracle APEX Report)

SELECT QUESTION_ID,
       RESPONDENT,
       ANSWER
FROM   SURVEY

Here is the result :

Question_ID Respondent  Answer
1           A           test1 
2           A           test2
3           A           test3
1           B           test4
2           B           test5
3           B           test6

The result I want is this :

                   Question
Respondant   1        2       3
A          test1    test2   test3
B          test4    test5   test6

How can this be achieved?

PIVOT is a bit more general than transposing, but yes, transposing is one of the applications. Because PIVOT is an aggregate operation, you must use an aggregate function (like MIN or MAX) even when it doesn't seem necessary, as the MIN(answer) in the solution I offered. — user5683823
– user5683823, Commented Oct 13, 2016 at 13:15

user5683823 · Accepted Answer · 2016-10-13 13:10:27Z

1

select *
from table_name
pivot ( min(answer) for question_id in (1 as q1, 2 as q2, 3 as q3));

answered Oct 13, 2016 at 13:10

user5683823

Sign up to request clarification or add additional context in comments.

2 Comments

user5683823 Over a year ago

Perhaps. The number of questions must be known beforehand though, it can't be variable. And the question_id must be known too (such as, it should run from 1 to 90, or perhaps from 3001 to 3090, etc. - the id's must be known when you write the query). However, with 90 questions, why would you want a format with 90 columns?

Farzad Soltani Over a year ago

The ids are known, from 1 to 90. I used a third party application to insert data in my table and this is how it inserted it. and my customer needs a refinement on the data. otherwise I would never design like this. thanks for the help. giving it a go now, it if works will mark as the correct answer,

Collectives™ on Stack Overflow

Pivot/Transpose in SQL Query in Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related