Transpose each row in SQL SELECT

Question

The following SQL

SELECT concat('CSE_', course_code), concat('CS_', course_code)
FROM classes

this returns some like this:

CSE_210   CS_210
CSE_570   CS_570
CSE_480   CS_480

Instead I need

CSE_270
CS_270
CSE_570
CS_570
CSE_480
CS_480

i.e. each row transposed. exactly in that order.

What is the best way to implement this using just SQL?

Answer 1

The simplest method is union all:

SELECT concat('CSE_', course_code)
FROM classes
UNION ALL
SELECT concat('CS_', course_code)
FROM classes;

However, it is more efficient typically to use a CROSS JOIN:

SELECT concat(v.prefix, c.course_code)
FROM classes c CROSS JOIN
     (VALUES ('CSE_', 1), ('CS_', 2)) v(prefix, ord)
ORDER BY c.course_code, v.ord;