Suppose we have a SELECT id FROM A; query SELECT id FROM A;

Which gives out

id 1 1 1 2 2 3

Now we need to split this column in half and draw the following conclusion:

id|id1 1|2 1|2 1|3

How to do it? I tried to crank it through CASE:

select case when id < 2 then 1 end id,
case when id >= 2 then id end id1 from A

Naturally, this code is tied to a specific table, in which the first half of the id = 1 values ​​and the output is incorrect: instead of the desired one, it gives:

id|id1 1| 1| 1| |2 |2 |3

Here it comes to the head NTILE , which divides (numbers) the column into equal parts, depending on what number you write in it. We try:

select id, ntile(2) over(Order by id) part from A

The conclusion is:

id|part 1|1 1|1 1|1 2|2 2|2 3|2

So, something clears up, it remains to figure out how to break a column in two using NTILE . Here I have problems. Is it possible to make CASE based on the value of NTILE ? If so, how can I finally get what is necessary with this approach?

  • What kind of DBMS? In some of which there are functions like pivot which from the "long" table make the "wide" table. If there is no pivot, then you will most likely have to do it through the case - that's about the same question for mysql stackoverflow.com/questions/7674786/mysql-pivot-table - Batanichek
  • Oracle is desirable. - daydark

1 answer 1

Well, if ntile, then something like that you can try:

 select min(id), max(id) from ( select id, row_number() over(partition by ntl order by id) grp from ( select id, ntile(2) over(order by id) ntl from A ) ) group by grp