Manual pivoting without using pivot in Oracle SQL

Question

I have a requirement to pull out data from a table which looks something like below:

SQL> SELECT * FROM weekly_test;

The data to be represented should be manually pivoted and if you notice there is review week 3 missing from the data.

I used the below sql to pivot it:

SELECT CASE
         WHEN rev_week = 1 THEN
          question
       END q1,
       CASE
         WHEN rev_week = 1 THEN
          answer
       END a1,
       CASE
         WHEN rev_week = 2 THEN
          question
       END q2,
       CASE
         WHEN rev_week = 2 THEN
          answer
       END a2,
       CASE
         WHEN rev_week = 3 THEN
          question
       END q3,
       CASE
         WHEN rev_week = 3 THEN
          answer
       END a3,
       CASE
         WHEN rev_week = 4 THEN
          question
       END q4,
       CASE
         WHEN rev_week = 4 THEN
          answer
       END a4
FROM weekly_test;

The result came like above but I want to display for review week 3 the value 'Not Completed' which is currently not possible as the data doesn't exist.

So the question is how can I display all weeks from 1-4 with the question and answer even when the data for that week doesn't exist?

Answer 1

First, to collapse your data down for a proper pivot, you need a GROUP BY on a non-pivoting item that will define your rows (I suggest question, since you appear to want to have a separate row per question), and you'll need to add aggregation (MAX()) around your pivoting conditionally-presented column(s) (here, answer).

For the conditionality you can certainly use CASE as you are doing, but in these situations due to the copy-paste and repetition you might find DECODE more terse and taking up a lot less space.

Finally, you can handle missing values with the NVL function that replaces NULL with something else. Put it all together, and it can be pretty short and sweet:

SELECT question,
       NVL(MAX(DECODE(rev_week,1,answer)),'Not Completed')  a1,
       NVL(MAX(DECODE(rev_week,2,answer)),'Not Completed')  a2,
       NVL(MAX(DECODE(rev_week,3,answer)),'Not Completed')  a3,
       NVL(MAX(DECODE(rev_week,4,answer)),'Not Completed')  a4
FROM weekly_test
GROUP BY question
ORDER BY question