Re-order columns values based on values in a table

Question

I would like know, how can we re-order the columns in ascending or descending order, while retrieving the data.

Suppose the table contains the data as below with no primary key/unique key constraints:

Col1    Col2    Col3     Col4
----    ----    ----     ----
B        D       C        A
C        Y       M        T
3        5       2        4
5        2       10       7

I want the output in below format: Each row data should be in ascending order, Whether it's Varchar or Numeric. That I guess could be done by taking ascii value of the value set? Is it achievable, if not why?

ColA    ColB    ColC     ColD
----    ----    ----     ----        
A        B       C        D
C        M       T        Y
2        3       4        5
2        5       7        10

Please try to provide a solution, which can be generic for any number of columns.

Every row should be re-arranged in ascending order, while we are retrieving the data. I am trying to do something like order by, but not based on column, but row. Is it achievable. It was an interview question for me by Amazon. I tried pivot and unpivot, but haven't been able to achieve this.

More information:

1. All column datatypes are VARCHAR2.
2. No specific order/pattern for the values.
3. The column names does not need to be same, while retrieving the data. Column names can be aliased or can be taken as per your preference. The data retrieved should be in the ascending order by row.
4. No primary key/unique key constraints.

I would like to understand, if such a solution is achievable, whether inefficient or not. Looking for answers using Oracle 11g or higher.

Answer 1

Solution

select  ColA,ColB,ColC,ColD

from   (select  t.n,t.val

               ,row_number () over 
                (
                    partition by n 
                    order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end  
                                ,val
                ) as rn

        from   (select      rownum as n
                           ,t.* 

                from        t
                ) t unpivot (val for col in (COL1,COL2,COL3,COL4)) t

        ) t pivot (min(val) for rn in ('1' as ColA,'2' as ColB,'3' as ColC,'4' as ColD))

order by n                        
;

For this demo, the numeric values are assumed to be integers.

Walkthrough

• Add a row number to each row:

  select      rownum as n
             ,t.* 
  
  from        t
  ;
  

  The row numbers will serve as identifiers to be later used when the unpivoted rows are pivoted back. The output produced is like this:

  +---+------+------+------+------+
  | N | COL1 | COL2 | COL3 | COL4 |
  +---+------+------+------+------+
  | 1 | B    | D    | C    | A    |
  +---+------+------+------+------+
  | 2 | C    | Y    | M    | T    |
  +---+------+------+------+------+
  | 3 | 3    | 5    | 2    | 4    |
  +---+------+------+------+------+
  | 4 | 5    | 2    | 10   | 7    |
  +---+------+------+------+------+
  

• Unpivot the rows.

  unpivot (val for col in (COL1,COL2,COL3,COL4)) t
  

• Rank the columns within each row according to their order as sorted first by their numeric value, if applicable, and then alphabetically as strings.

  row_number () over 
  (
      partition by n 
      order by     case when regexp_like(val,'^-?\d+$') then to_number(val) end  
                  ,val
  ) as rn
  

  For numeric items, the first sorting criterion will evaluate to corresponding numeric values and thus determine the order of the items. For string items, the first term will be null, and so the order will be determined by the second sorting criterion.

  The output that you will get for your example after unpivoting and ranking will be this:

  +---+-----+----+
  | N | VAL | RN |
  +---+-----+----+
  | 1 | A   | 1  |
  +---+-----+----+
  | 1 | B   | 2  |
  +---+-----+----+
  | 1 | C   | 3  |
  +---+-----+----+
  | 1 | D   | 4  |
  +---+-----+----+
  | 2 | C   | 1  |
  +---+-----+----+
  | 2 | M   | 2  |
  +---+-----+----+
  | 2 | T   | 3  |
  +---+-----+----+
  | 2 | Y   | 4  |
  +---+-----+----+
  | 3 | 2   | 1  |
  +---+-----+----+
  | 3 | 3   | 2  |
  +---+-----+----+
  | 3 | 4   | 3  |
  +---+-----+----+
  | 3 | 5   | 4  |
  +---+-----+----+
  | 4 | 2   | 1  |
  +---+-----+----+
  | 4 | 5   | 2  |
  +---+-----+----+
  | 4 | 7   | 3  |
  +---+-----+----+
  | 4 | 10  | 4  |
  +---+-----+----+ 
  

• Pivot the rows back using the rankings as new column names (optionally aliased following any pattern you like; in the query above – as ColA, ColB etc.) and the row numbers assigned at the beginning as row identifiers.

  The result will be the expected output:

  +------+------+------+------+
  | COLA | COLB | COLC | COLD |
  +------+------+------+------+
  | A    | B    | C    | D    |
  +------+------+------+------+
  | C    | M    | T    | Y    |
  +------+------+------+------+
  | 2    | 3    | 4    | 5    |
  +------+------+------+------+
  | 2    | 5    | 7    | 10   |
  +------+------+------+------+
  

A live demo is available at Rextester.

Answer 2

This is an oracle solution, made certain assumption, but you can modify to your test.

• 4 columns as described
• max length of data is 20 chars

And here is my code

drop table test1 purge;
Create table test1 (col1 varchar2(20),col2 varchar2(20), col3 varchar2(20), col4 varchar2(20));
insert into test1 values('B','D','C','A');
insert into test1 values('C','Y','M','T');
insert into test1 values('3','5','2','4');
insert into test1 values('5','2','10','7');
commit;
-- results as desited by OP
set sqlformat csv
set echo on
with x as (select rownum r, lpad(col1,20) col1, lpad(col2,20) col2, lpad(col3,20) col3, lpad(col4,20) col4 from test1)
,y as (select * from x unpivot (val for c in (col1, col2, col3, col4)) order by r, val)
,z as (select r, listagg(val,',') within group (order by val) as rlist from y group by r)
select ltrim(regexp_substr(rlist,'[^,]+',1,1)) as col1,ltrim(regexp_substr(rlist,'[^,]+',1,2)) as col2
,ltrim(regexp_substr(rlist,'[^,]+',1,3)) as col3,ltrim(regexp_substr(rlist,'[^,]+',1,4)) as col4
from z;

Output looks like this (i am using sql developer),

"COL1","COL2","COL3","COL4"
"A","B","C","D"
"C","M","T","Y"
"2","3","4","5"
"2","5","7","10"