CODEWITHSUNDEEP
sql-serversql-server-2012union
Shanawaz Khan
Shanawaz Khan

Reputation: 11

SQL Server : add a new column using union

I have 2 tables which have 2 columns in common and 1 column is different in both table

Table A

enter image description here

Table B

enter image description here

I need to create a common table having the values as follows

Expected Output

enter image description here

I tried using join on Memid and Meas but it duplicates as the 2 field do not create unique set as shown in figure

I tried union but then I get a resultset like this

enter image description here

Output for Inner join with distinct condition

enter image description here

How do I go about achieving the desired result set?

Note: Just a note coincidentally in this case the 2 columns seems to have similar values but they can be different.

Basically I need to create this one table with the 4 columns where Payer and PPayer columns should be independent of each other.

Upvotes: 0

Views: 1777

Answers (2)

Nikunj Satasiya
Nikunj Satasiya

Reputation: 1043

Shanawaz Khan, Try this Solution

Declare Sample Table

DECLARE @A as TABLE(
UserId INT,
DEPT VARCHAR(50),
ROOM INT)

DECLARE @B as TABLE(
UserId INT,
DEPT VARCHAR(50),
LAB VARCHAR(50))

Insert Sample Records in Created Table

INSERT INTO @A (UserId,DEPT,ROOM) VALUES(1,'A',1),(1,'B',1),(1,'A',2),(1,'B',2)
INSERT INTO @B (UserId,DEPT,LAB) VALUES(1,'A','P'),(1,'B','Q'),(1,'A','P'),(1,'B','Q')

Generate DEPT wise Row number for Both Tables and Insert into another Temptable

SELECT ROW_NUMBER() OVER(PARTITION BY A.DEPT ORDER BY A.ROOM ) AS Rno,* INTO #tbl_A FROM @A A 
SELECT  ROW_NUMBER() OVER(PARTITION BY B.DEPT ORDER BY B.LAB) AS Rno,* INTO #tbl_B FROM @B B

Final Query Using Inner Join

SELECT  A.UserId,A.DEPT,A.ROOM,B.LAB FROM #tbl_A  AS A
INNER JOIN #tbl_B AS B ON A.Rno =B.Rno AND A.DEPT =B.DEPT ORDER BY A.ROOM, B.DEPT

Drop Created Temptable

DROP TABLE #tbl_A,#tbl_B

OutPut

enter image description here

Upvotes: 0

PSK
PSK

Reputation: 17943

You don't need to use UNION, you can try like following using INNER JOIN.

INSERT INTO NewTable (
    UserId
    ,DEPT
    ,ROOM
    ,LAB
    )
SELECT DISTINCT ta.UserId
    ,ta.DEPT
    ,ta.ROOM
    ,tb.LAB
FROM TableA ta
INNER JOIN TableB tb ON ta.UserId = tb.UserId
    AND ta.DEPT = tb.DEPT

Check Working Demo

Upvotes: 1

Related Questions