Skip to content
Advertisement

How to sort columns values in sql db2-400?

I have 10 columns with similar information, and I would like to sort them asc when greater than 0.
For instance, from this:

A1 A2 A3 A4 A5 A6 A7 A8 A9 A0
00 01 00 09 08 05 00 02 06 03

To this:

A1 A2 A3 A4 A5 A6 A7 A8 A9 A0
01 02 03 05 06 08 09 00 00 00

I been trying through min and max functions to no avail.

Advertisement

Answer

You can unpivot the data and reaggregate: One way is:

select max(case when seqnum = 1 then a end) as a1,
       max(case when seqnum = 2 then a end) as a2,
       . . .
from (select a.a,
             row_number() over (order by a desc) as seqnum
      from (select a1 as a from t union all
            select a2 from t union all
            select a3 from t union all
            . . .
           ) a
     ) a;
        
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement