RAJ February 2016

Split a column into multiple columns

select distinct account_num from account order by account_num;

The above query gave the below result

                 account_num
                      1
                      2
                      4
                      7
                      12
                      18
                      24
                      37
                      45
                      59

I want to split the account_num column into tuple of three account_num's like (1,2,4);(7,12,18);(24,37,45),(59); The last tuple has only one entry as there are no more account_num's left. Now I want a query to output the min and max of each tuple. (please observe that the max of one tuple is less than the min of the next tuple). Output desired is shown below

                 1  4
                 7  18
                 24 45
                 59 59    

Edit: I have explained my requirement in the best way I could

Answers


Bozo Tegeltija February 2016

Please add more information on what you want to do. What is the connection between account_number 1 and number 4, 7 and 18? Is there any? If not, why would you want to split this into two columns and what is the rule for splitting it? With what you have posted, you could do something like this:

select 1 as account_num, 4 as account_num1 from dual
union all select 7 as account_num, 18 as account_num1 from dual
...

and so on, but I don't see the use for this.


Emil Moise February 2016

You can use the example below as a scratch, this is only based on information you have provided so far. For further documentation, you can consult Oracle's analytical functions docs:

with src as( --create a source data
  select 1 col from dual union
  select 2  from dual union
  select 4  from dual union
  select 7  from dual union
  select 12 from dual union
  select 18 from dual union
  select 24 from dual union
  select 37 from dual union
  select 45 from dual union
  select 59 from dual
)
select 
    col, 
    decode(col_2, 0, max_col, col_2) col_2 -- for the last row we get the maximum value for the row
  from (
    select 
      col, 
      lead(col, 2, 0) over (order by col) col_2, -- we get the values from from two rows behind
      max(col) over () max_col, -- we get the max value to be used for the last row in the result
      rownum rn from  src -- we get the rownum to handle the final output
  ) where mod(rn - 1, 3) = 0 -- only get rows having a step of two


Stepan Kasyanenko February 2016

This is another solution.

SELECT *
FROM (SELECT DISTINCT MIN(val) over(PARTITION BY gr) min_,
                                            MAX(val) over(PARTITION BY gr) max_
                FROM (SELECT val,
                                         decode(trunc(rn / 3), rn / 3, rn / 3, ceil(rn / 3)) gr
                                FROM (SELECT val,
                                             row_number() over(ORDER BY val) rn
                                                FROM (select distinct account_num from account order by account_num)))) ORDER BY min_

UPDATED

Solution without analytic function.

SELECT MIN(val) min_,
       MAX(val) max_
FROM (SELECT val,
             ceil(rn / 3) gr
            FROM (SELECT val,
                         rownum rn
                        FROM A_DEL_ME)) GROUP BY gr

Post Status

Asked in February 2016
Viewed 2,509 times
Voted 7
Answered 3 times

Search




Leave an answer