CODEWITHSUNDEEP
sqloracle-databasepivotoracle-sqldeveloper
Programmermid
Programmermid

Reputation: 598

Oracle Pivot Based on Multiple Columns

I am fairly new to Pivoting and trying to Pivot based on two columns.

Data I have:

enter image description here

Data I want to Achieve after pivoting:

enter image description here

My Query which is Flawed:

   select *
from
(
  select ISSUEID,ANSWER, ANSWERCOMMENT,QUESTION ,QUESTIONID
  from issue_survey
  WHERE ISSUEID = 6877
) d
pivot
(
  max(QUESTION)
  for QUESTIONID  in (1 QUESTION1,2 QUESTION2, 3 QUESTION3)
) piv;

The Results I am getting with this flawed query:

enter image description here

Any suggestions with this are appreciated. Thank you!

Upvotes: 2

Views: 127

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Try:

select *
from
(
  select ISSUEID,ANSWER, ANSWERCOMMENT,QUESTION ,
         rownum rn
  from issue_survey
  WHERE ISSUEID = 6877
) d
pivot
(
  max(QUESTION) as question, max(Answer) as answer,
  max( ANSWERCOMMENT ) as ANSWERCOMMENT
  for rn  in ( 1 ,2 , 3 )
) piv;

Demo: http://www.sqlfiddle.com/#!4/e5aba7/6

| ISSUEID |        1_QUESTION | 1_ANSWER | 1_ANSWERCOMMENT |   2_QUESTION | 2_ANSWER | 2_ANSWERCOMMENT |      3_QUESTION | 3_ANSWER | 3_ANSWERCOMMENT |
|---------|-------------------|----------|-----------------|--------------|----------|-----------------|-----------------|----------|-----------------|
|    6877 | Do you wanna wait |      YES |           TEST1 | How about it | Its okay |           TEST2 | Sample question |   (null) |           TEST3

Upvotes: 1

Related Questions