CODEWITHSUNDEEP
sql-server
FrankTan
FrankTan

Reputation: 1686

Combine two tables in SQL Server

I have tow tables with the same number of rows

Example:

table a:

1,A
2,B
3,C

table b:

AA,BB
AAA,BBB,
AAAA,BBBB

I want a new table made like that in SQL SErver:

1,A,AA,BB
2,B,AAA,BBB
3,C,AAAA,BBBB

How do I do that?

Upvotes: 1

Views: 757

Answers (3)

Betlista
Betlista

Reputation: 10549

Your query is strange, but in Oracle you can do this:

select a.*, tb.*
  from a
     , ( select rownum rn, b.* from b ) tb -- temporary b - added rn column
 where a.c1 = tb.rn -- assuming first column in a is called c1

if there is not column with numbers in a you can do same trick twice

select ta.*, tb.*
  from ( select rownum rn, a.* from a ) ta
     , ( select rownum rn, b.* from b ) tb
 where ta.rn = tb.rn

Note: be aware that this can generate random combination, for example

1 A AA BB
2 C A B
3 B AAA BBB

because there is no order by in ta and tb

Upvotes: 0

marc_s
marc_s

Reputation: 754488

In SQL Server 2005 (or newer), you can use something like this:

-- test data setup
DECLARE @tablea TABLE (ID INT, Val CHAR(1))
INSERT INTO @tablea VALUES(1, 'A'), (2, 'B'), (3, 'C')

DECLARE @tableb TABLE (Val1 VARCHAR(10), Val2 VARCHAR(10))
INSERT INTO @tableb VALUES('AA', 'BB'),('AAA', 'BBB'), ('AAAA', 'BBBB')

-- define CTE for table A - sort by "ID" (I just assumed this - adapt if needed)
;WITH DataFromTableA AS
(
    SELECT ID, Val, ROW_NUMBER() OVER(ORDER BY ID) AS RN
    FROM @tablea
), 
-- define CTE for table B - sort by "Val1" (I just assumed this - adapt if needed)
DataFromTableB AS 
(
    SELECT Val1, Val2, ROW_NUMBER() OVER(ORDER BY Val1) AS RN
    FROM @tableb
)
-- create an INNER JOIN between the two CTE which just basically selected the data
-- from both tables and added a new column "RN" which gets a consecutive number for each row
SELECT
    a.ID, a.Val, b.Val1, b.Val2
FROM 
    DataFromTableA a
INNER JOIN
    DataFromTableB b ON a.RN = b.RN

This gives you the requested output:

enter image description here

Upvotes: 1

Kreg
Kreg

Reputation: 647

You could do a rank over the primary keys, then join on that rank:

SELECT RANK() OVER (table1.primaryKey),
  T1.*,
  T2.*
FROM 

SELECT T1.*, T2.*
FROM
(
  SELECT RANK() OVER (table1.primaryKey) [rank], table1.* FROM table1
) AS T1
JOIN
(
  SELECT RANK() OVER (table2.primaryKey) [rank], table2.* FROM table2
) AS T2 ON T1.[rank] = T2.[rank]

Upvotes: 0

Related Questions