Sql: Sorting row entries alphanumerically

Question

I have the following table in an vertica db:

+-----+-------+-------+-------+-------+
| Tid | Item1 | Item2 | Item3 | Item4 |
+-----+-------+-------+-------+-------+
|   1 | A     | B     | C     | D     |
|   2 | A     | E     | B     | D     |
|   3 | D     | A     | 5     | C     |
|   4 | B     | 1     | E     | A     |
+-----+-------+-------+-------+-------+

My goal is it to sort the rows in alphanumerically, all entries are strings. The result would look like that:

+-----+-------+-------+-------+-------+
| Tid | Item1 | Item2 | Item3 | Item4 |
+-----+-------+-------+-------+-------+
|   1 | A     | B     | C     | D     |
|   2 | A     | B     | D     | E     |
|   3 | 5     | A     | C     | D     |
|   4 | 1     | A     | B     | E     |
+-----+-------+-------+-------+-------+

Usage: I am using sql to generate an apriori algorithm, in order to generate new candidates I would like to use a F_k-1 join, which in my understanding needs sorting like requested above.

Thanks for the help.

Answer 1

I Don't have access to Vertica but this should do the job (although I can't grantee that this is the most efficient way / cleanest code for Vertica)

select      Tid
           ,min (case rn when 1 then item end)  as Item1
           ,min (case rn when 2 then item end)  as Item2
           ,min (case rn when 3 then item end)  as Item3
           ,min (case rn when 4 then item end)  as Item4

from       (select      Tid,item
                       ,row_number () over (partition by Tid order by item) as rn

            from       (            select Tid ,Item1 as item from t
                        union all   select Tid ,Item2         from t
                        union all   select Tid ,Item3         from t
                        union all   select Tid ,Item4         from t
                        ) t
            ) t

group by    Tid

---

+-----+-------+-------+-------+-------+
| tid | item1 | item2 | item3 | item4 |
+-----+-------+-------+-------+-------+
| 1   | A     | B     | C     | D     |
+-----+-------+-------+-------+-------+
| 2   | A     | B     | D     | E     |
+-----+-------+-------+-------+-------+
| 3   | 5     | A     | C     | D     |
+-----+-------+-------+-------+-------+
| 4   | 1     | A     | B     | E     |
+-----+-------+-------+-------+-------+