There is a similar question here.
You can achieve this using
CROSS APPLY with
select c.x from [mytable] cross apply ( select [Name] union all select CAST([Age] as varchar) union all --all the columns must be of the same type select [Type] union all select [City] ) c (x) where x is not null ;
As pointed out by @Andriy M it could be also written like this:
select x from [mytable] cross apply (VALUES ([Name]), (CAST([Age] as varchar)), ([Type]), ([City])) c(x) -- parentheses are required WHERE x IS NOT NULL
For more detail see here.
The above query does not guarrantee any particular order of records in the resultset – to get a particular order of records in a resultset it is allways necessary to use the
ORDER BY clause.
The issue here is mostly order. Otherwise you could just do
SELECT Name FROM Table UNION ALL SELECT Age...
So let’s assign some values to order by:
--This order is arbitrary, and will order the rows in whatever order they come. WITH NumberedRows AS (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rownum FROM Table ) --Now do a union all with things to column numbers too. Alias the first set, and cast Age to a string , Unioned AS (SELECT rownum, Name as Value, 1 as ColOrder FROM NumberedRows UNION ALL SELECT rownum, CAST(Age AS VARCHAR(3)), 2 as ColOrder FROM NumberedRows UNION ALL SELECT rownum, [Type], 3 as ColOrder FROM NumberedRows UNION ALL SELECT rownum, City, 4 as ColOrder FROM NumberedRows ) --Now strip the nulls and extra columns, and order SELECT Value FROM Unioned WHERE Value IS NOT NULL ORDER BY rownum, ColOrder;