How to split an SQL Table into half and send the other half of the rows to new columns with SQL Query?

Question

Country    Percentage

India      12%
USA        20%
Australia  15%
Qatar      10%

Output :

Country1    Percentage1     Country2     Percentage2
India       12%             Australia    15%
USA         20%             Qatar        10%

For example there is a table Country which has percentages, I need to divide the table in Half and show the remaining half (i.e. the remaining rows) in the new columns. I've also provided the table structure in text.

Answer 1

First, this type of operation should be done at the application layer and not in the database. That said, it can be an interesting exercise to see how to do this in the database.

I would use conditional aggregation or pivot. Note that SQL tables are inherently unordered. Your base table has no apparent ordering, so the values could come out in any order.

select max(case when seqnum % 2 = 0 then country end) as country_1,
       max(case when seqnum % 2 = 0 then percentage end) as percentage_1,
       max(case when seqnum % 2 = 1 then country end) as country_2,
       max(case when seqnum % 2 = 1 then percentage end) as percentage_2       
from (select c.*,
             (row_number() over (order by (select null)) - 1) as seqnum
      from country c
     ) c
group by seqnum / 2;