SQL: Turn multiple rows into one row multiple columns

We have a table looks like this:
Name Question Value
Alan        1           5
Alan        2           4
Alan        3           6
Jim          1           4
Jim          2           3
Jim          4           5

We would like to select out a result looks like this:
Name    Q1     Q2    Q3    Q4
Alan       5        4       6      null
Jim          4       3       null   5

This problem seems to be very easy, however, cost me a lot of time to figure out how to proceed. Finally, after some googling a nice post introduce a nice way to handle similar issues using MAX(DECODE()) function.

Basically, you first need to utilise the decode() function. decode() acts like if-else-then. For example, decode(name, 'Alan', 'True', 'False') works like if the name is 'Alan' then return 'True', otherwise return 'False'.

In our problem here, we use decode() to distinguish the answers from different questions: decode(question, 1, value, NULL) as Q1,   decode(question, 2, value, NULL) as Q2, ... In this way we would pick out the value of each question.

After we can get the answer to each question, what we need to do is group the answers by different names. That's why we need max() here to act for the group by clause. Since there is only one value here for each question, so max(), min() or some other reasonable functions are all working well here.:

select name AS NAME, max(decode(question, 1, value, NULL)) AS Q1, max(decode(question, 2, value, NULL)) AS Q2, max(decode(question, 3, value, NULL)) AS Q3, max(decode(question, 4, value, NULL)) AS Q4 from table group by name.

Oracle: To convert a row into a column (CROSS-JOIN)

This afternoon, while I am doing some Jasper report stuff, I am puzzled by a SQL query that which takes quite a long time to execute. As the time goes, Jasper will easily get a timeout and a blank screen is waiting for me after retrieving data for about 120 seconds.

As it is not appropriate to use the original data table as the example to describe here, I tried to make up a similar scenario which should explain the cause and the solution sufficiently.

Given a table A, with five columns -- Primary Key (pk), Statistics 1A (s1a), Statistics 1B (s1b), Statistics 2A(s2a), Statistics 2B(s2b), we need to select the data out into a form that looks like -- Primary Key (pk), Statistics A(s1a or s2a), Statistics B(s2b or s2b). Essentially, it is trying to convert a row into one column but two rows.  Someone may prefer to call it pivot query by the way.

Initially, naively I think the most easiest and intuitive way to do it is by two union queries. That is
select pk, s1a, s1b from table where *** union select pk, s2a, s2b from table where ***.
When the data set is small, and it only depends on the single table, it is fine. However, in my case, the data set is huge, and the union will not be two but eight, and more importantly every union section will consists of another 6 inner join tables. That's the reason why Jasper cannot retrieve what it needs on time.

In this link, as suggested by Scott, introduced a very smart way to handle this issue. What it uses is called: cross join. So basically, we use the existing table to cross join with the different type/criteria to return the expected result.

select
   pk,
   case
      when ite = 's1' then s1a
      when ite = 's2' then s2a
   end as sa
   case
      when ite = 's1' then s1b
      when ite = 's2' then s2b
   end as sb
 from
   (
       select pivoter.ite,
           s1a, s1b, s2a, s2b
       from
           table
           cross join (
               select 's1' as ite from dual
               union all
               select 's2' as ite from dual
           ) pivotel
   )

By using the above code, the problem will be solved by the cross join union columns.

Miscellaneous Points for Oracle Join Query

As a software developer, you must have the experience to deal with all sorts of join queries. And I believe everyone has once or still now struggle with all these different terms about inner, outer, left, right, and etc. As I have spent roughly two hours these morning, to summarize some of the points that I am easily to forgotten and got wrong, it's a good chance to write it done in case later I need to test my memory.

1) What is the difference of join...on... grammar and little (+) sign?

Oracle used to only support (+) in old days, which is also kind of created by Oracle. Later, as the ANSI formalized the standard for join grammar, Oracle adopted both for join queries. As a result, it won't be a surprise if you see both two styles in your project which comes from different developers.

2) Any difference between inner join and join?

No.

3) Why do we need outer join?

Generally, inner join will return result sets that have a match in both Table A and B. However in reality sometimes, we also need results to be returned even the matching is null. That's the place where outer join shows its ability. There are plenty of tutorials available to discuss about the outer join. What I want to mentioning here is the null value will only applied to the join's destination table, but not the starting table.

4) Any more differences?

Yes. Actually there are two more I want to emphasize:

1. ANSI style supports outer join, which you can google what does it mean. But traditional Oracle doesn't directly support it. ( By saying it directly, I mean there always exists workarounds, and various workarounds)
2. One of the most important feature to differentiate the two is the ANSI style separate the join condition and query filter criteria, which is much more nice tidy and clean. (Is it, at least I think so). Meanwhile, you will also be able to avoid a lot of caveats that may come with you when you go for the traditional style. See Common errors seen when using OUTER-JOIN.