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;