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 https://blog.sqlauthority.com/ )
18 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
Yes, Union is for better performance if you’re assuming there’s no duplicate data.
I mean Union all
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’)
— ——————————————
HI Pinal, i have one questions.
how to identify the missing identity values of a table ?
Pinal, Union isn’t going to sort the results. To guarantee sorted results, you need to explicitly have the ORDER BY statement.
By default UNION Will Sort and UNION ALL will not Sort.
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]
UserId | Recid
37 18
12 37
If i Log in with userid 37,
I want to output like,
Id
18
12
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
Hi,
How to get the same result as above if the two columns are from two different tables ?
Thanks
RAJ
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.
If you can provide me table creation script and sample data insert along with expected output, I am sure others can also help.
Thank you so much.
This was exactly what I was needing.