SQL SERVER – Merge Two Columns into a Single Column

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)

About these ads

15 thoughts on “SQL SERVER – Merge Two Columns into a Single Column

  1. you can also use Union all for better performance.If [ Union/Union all ] can’t be use in certain example then Unpivot can be use in complex situation.
    like,

    Declare @t table(Column1 int,Column2 int)
    insert into @t values(1,5),(2,6),(3, 7),(4,8)

    Select orders from
    (select * from @t)p
    UNPIVOT
    (Orders FOR Column3 IN
    (Column1,Column2)
    )AS unpvt
    order by Orders

        • It should also be pointed out that if there is not an assumption of no duplicate data but one wants to filter out the duplicates and get just the unique values, UNION would be necessary – UNION ALL would not return the correct result – with the stated assumption UNION ALL would be fine (except for the sort as you mentioned, Pinal), but if one couldn’t assume that, UNION ALL would not return the correct set of values.

  2. Quiz for a chance to win a Quadcopter Drone – Brain Teasers
    /*
    Contest Part 1: Brain Teasers
    There are two questions for you in this part of the contest.
    Question: There are two 7s. How will you write select statement with a single operator that returns single 7?
    Hint: SELECT 7(Answer)7
    */
    SELECT 7 | 7
    – —————————————–
    /*
    Question: Write down the shortest code that produces 1 without using any numbers in the select statement?
    Hint: SELECT (Answer)
    */
    select LEN(‘a’)
    – ——————————————

  3. Pinal, Union isn’t going to sort the results. To guarantee sorted results, you need to explicitly have the ORDER BY statement.

  4. Not an optimal solution, jst to provide another solution
    Select case when Col1 is null then Col2 else col1 end ‘Union’ from #TestTable group by Col1,col2 with cube having Col2 is not null order by [Union]

    • Like so:
      USE [zandbak]
      GO

      CREATE TABLE [dbo].[Table_1](
      [Userid] int
      , [RecId] int
      );
      GO

      INSERT INTO [dbo].[Table_1] (
      [Userid]
      , [RecId])
      VALUES (37, 18 )
      , (12, 37)
      , (35, 20 )
      , (34, 11);

      DECLARE
      @uid int = 37;

      SELECT
      CASE @uid
      WHEN [userid] THEN [recid]
      WHEN [recid] THEN [userid]
      ELSE NULL
      END AS id
      FROM [zandbak].[dbo].[Table_1]
      WHERE @uid IN ([userid], [Recid]);
      – ————[output:]————
      id
      18
      12

    • declare @t table(UserId int, Recid int)
      insert into @t values(37,18),(12,37)
      Declare @input int=37
      select case when userid=@input then Recid else userid end from @t

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s