Re-order columns values based on values in a table

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:

• All column datatypes are VARCHAR2.

• No specific order/pattern for the values.

• 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.

• 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.

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+

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+

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.) 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:

%%CODEBLOCK_1%%

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

%%CODEBLOCK_2%%

- 
Unpivot the rows.

%%CODEBLOCK_3%%

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

%%CODEBLOCK_4%%

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:

%%CODEBLOCK_5%%

- 
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:

%%CODEBLOCK_6%%

A live demo is available at Rextester.) 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:

%%CODEBLOCK_5%%

-
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:

%%CODEBLOCK_6%%

A live demo is available at Rextester.) 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:

%%CODEBLOCK_1%%

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

%%CODEBLOCK_2%%

-
Unpivot the rows.

%%CODEBLOCK_3%%

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

%%CODEBLOCK_4%%

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:

%%CODEBLOCK_5%%

-
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:

%%CODEBLOCK_6%%

A live demo is available at Rextester.

StackExchange Database Administrators Q#154820, answer score: 3