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.

SQL SERVER - Merge Two Columns into a Single Column mergecols1

Here is the result table.

SQL SERVER - Merge Two Columns into a Single Column mergecols2

Reference: Pinal Dave https://blog.sqlauthority.com/ )

Previous Post
SQL SERVER – Parsing SSIS Catalog Messages – Notes from the Field #030
Next Post
MySQL – Introduction to User Defined Variables

Related Posts

No results found.

17 Comments. Leave new

  • 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

    Reply
  • Yes, Union is for better performance if you’re assuming there’s no duplicate data.

    Reply
    • I mean Union all

      Reply
      • The problem with UNION ALL is that data will not be sorted and user wanted data to be sorted.

      • 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.

  • Kees de Boer
    May 30, 2014 12:34 pm

    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’)
    — ——————————————

    Reply
  • infosites4you
    May 30, 2014 3:05 pm

    HI Pinal, i have one questions.
    how to identify the missing identity values of a table ?

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

    Reply
  • 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]

    Reply
  • UserId | Recid
    37 18
    12 37

    If i Log in with userid 37,
    I want to output like,

    Id

    18
    12

    Reply
    • Kees de Boer
      July 15, 2014 2:29 pm

      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

      Reply
    • 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

      Reply
  • rajkumarpalnati
    August 27, 2014 6:19 pm

    Hi,
    How to get the same result as above if the two columns are from two different tables ?

    Thanks
    RAJ

    Reply
  • sir i want your help,
    i am having table name marks, and col names as subjects and total,
    the marks are inserted in the table like as below,

    tamil 80
    english 80
    maths 80
    science 80 like this.

    but i want to merge this like,

    tamil 80; english 80; maths 80; science 80.

    is this possible, if yes means kindly send query to my mail id (vinithkarthi7@gmail.com) or rly in this blog.

    Reply
    • If you can provide me table creation script and sample data insert along with expected output, I am sure others can also help.

      Reply

Leave a Reply

Menu