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.

• ypercubeᵀᴹ: What should happen if there are nulls?

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"

• Paul White: You could improve this with some description of the code and perhaps a link to an online demo?
• Andriy M: This query will sort numbers correctly but char strings of different length will be sorted in an unexpected way: longer strings after the shorter ones rather than just alphabetically. See demo.
• ypercubeᵀᴹ: @AndriyM it will also sort negative numbers in an unexpected way.
• Andriy M: @ypercubeTM: True, although I never thought of negative numbers being a possibility in the OP's setup until the other answer suggested the idea. For completeness, then, I should also add that this method will sort non-integer values unexpectedly as well.