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