Transposing rows to columns

Today, I saw a question in an orkut SQL forum where the guy wanted to transpose rows to columns.

I replied to it.

Let me explain about it.

See this snapshot. I do a query of my test2 table. There are 2 rows. And the 4 weekly data has to be extracted into single column.

See, there are 4 columns that need to be turned into 1 column in 4 rows. So we need to do a cartesian join 4 times. So I use this table below.

See the cartesian join done and the results. See that each ord_no appears 4 times.

Now, using decode, I am getting only what I need.

Pagination

Have you tried any query like this?
select * from time_dim where rownum between 1 and 5
What will be the answer?

Okay, what will be the answer for this one?
select * from time_dim where rownum between 2 and 5

Surprised? Rownum will return any row only if it starts with 1. Everything else will not return anything.

Okay, I guess you knew this part already. Then try this query.
select * from time_dim where rownum between 1 and 5 order by time_key asc

Do you think you will get the first 5 ranks ordered by time_key? Try it. You didnt. You got some 5 records ordered by time_key, not necessarily the top 5.

Why? Because rownum is a pseudo column that is assigned to rows that are fetched. The first 5 records that got fetched were returned with order by on time_key.

Then how do you make sure that you get the first 5 only?

For that you need something called pagination. Try this query
select * from
(
select rownum as rank, a.* from
(
select * from time_dim order by time_key
) a
) b
where rank=8

Here you passed ordered the data from the query in a sub-query. Then passed it to the outer one with the rownum and gave it an alias. Then you used it in the outer query to get that rank.

It is necessary to first order the data and then pass it to an outer query to use the rownum. If you use rownum from the same query where you ordered, it will not be ordered correctly.

Fetch data that fall in a rank range

This query returns all the rows that fall in a certain rank range. Please note that the second condition is rnum and not rownum. If you use rownum, nothing will be returned.

select * from
( select a.*, rownum rnum from
(select * from expense_fct where expense is not null order by expense desc) a
where rownum <= &upperbound )
where rnum >= &lowerbound