Return multiple column values as new rows SQL

Question

I had a table that shows each employee and lists the positions that they have applied for as separate columns.

APPNO, Date_Applied, FirstName, LastName, Position1, Position2, Position3

I would like to return the multiple positions applied for as additional rows for the same employee. So if one person applied for 3 different positions it would return 3 different rows for that one employee.

APPNO, Date_Applied, FirstName, LastName, Position1,
APPNO, Date_Applied, FirstName, LastName, Position2
APPNO, Date_Applied, FirstName, LastName, Position3

Answer 1

Normalization is really important, but something like this would work:

SELECT APPNO, Date_Applied, FirstName, LastName, Position1 AS position
UNION ALL
SELECT APPNO, Date_Applied, FirstName, LastName, Position2
WHERE Position2 IS NOT NULL
UNION ALL
SELECT APPNO, Date_Applied, FirstName, LastName, Position3
WHERE Position3 IS NOT NULL

Answer 2

Another way using CROSS APPLY:

SELECT  t.APPNO,
        t.Date_Applied,
        t.FirstName,
        t.LastName,
        x.Position
FROM YourTable t
CROSS APPLY 
(
    VALUES
        (t.Position1),
        (t.Position2),
        (t.Position3)
) x (Position)
WHERE x.Position IS NOT NULL;