Rotate T-SQL Single Row Column

Question

I have a single row table returned from a query that looks something like this:

[Date1] [Date2] [Date3] [Date4] [Date5] [Date6]

and I want all dates to add up as

[Date1]
[Date2]
[Date3]
[Date4]
[Date5]
[Date6]

How can I do this without a bunch of individual union requests and approvals? I tried playing with the PIVOT function, but I'm confused because there is nothing to aggregate the row.

Answer 1

Try using UNPIVOT, for example:

SELECT Dates
FROM 
    (SELECT * from yourtable) p
UNPIVOT
    (Dates FOR Seq IN 
        ([Date1], [Date2], [Date3], [Date4], [Date5], [Date6])
) AS unpvt