Here is a question which I have received from user yesterday.

Hi Pinal,

I want to build queries in SQL server that merge two columns of the table

If I have two columns like,

Column1 | Column2
 1                5
 2                6
 3                7
 4                8

I want to output like,

Column1
 1
 2
 3
 4
 5
 6
 7
 8

It is a good question. Here is how we can do achieve the task. I am making the assumption that both the columns have different data and there is no duplicate.

USE TempDB
GO
CREATE TABLE TestTable (Col1 INT, Col2 INT)
GO
INSERT INTO TestTable (Col1, Col2)
SELECT 1, 5
UNION ALL
SELECT 2, 6
UNION ALL
SELECT 3, 7
UNION ALL
SELECT 4, 8
GO
SELECT Col1
FROM TestTable
UNION
SELECT Col2
FROM TestTable
GO
DROP TABLE TestTable
GO


Here is the original table.

Here is the result table.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL