Geek asks and answers

How to combine columns from 2 tables to 1 without using JOIN

I am using SQL Server 2008. I have two table variables, such as

FirstName
==========
Little
John  
Baby  

LastName
==========
Timmy
Doe  
Jessica

And I want the result table:

 First      Last   
=====================
 Little     Timmy    
 John       Doe      
 Baby       Jessica

Note that PK cannot join the two tables. I am trying to use a cursor but don’t know how to start.

---- Updated -----

I know this is a rare case, but I am writing a script to clear outdated data. The only way we know Little is coming with Timmy is that they are the first record in the table. Would it help if we had a PC for tables, but there is no relation?

ID FirstName
==========
1  Little
2  John  
3  Baby  
----------

ID LastName
==========
4  Timmy
5  Doe  
6  Jessica
----------

I am not familiar with TSQL, so I thought that I could iterate over 2 tables, for example, iterate over arrays in memory.

+5
source share
5

- , :

SELECT FirstName AS First, LastName AS Last
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, FirstName
  FROM FirstName
) t1
INNER JOIN
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS RowNum, LastName
  FROM LastName
) t2
ON t1.RowNum = t2.RowNum

, .

+6

. , , , :

DECLARE @first TABLE(ID INT IDENTITY(1,1), NameFirst VARCHAR(30));
DECLARE @last TABLE(ID INT IDENTITY(1,1), NameLast VARCHAR(30));

-- Note that we don't need to list column names here
INSERT INTO @first VALUES('Little');
INSERT INTO @first VALUES('John');
INSERT INTO @first VALUES('Baby');

INSERT INTO @last VALUES('Timmy');
INSERT INTO @last VALUES('Doe');
INSERT INTO @last VALUES('Jessica');

SELECT n1.NameFirst
, n2.NameLast
FROM @first n1
INNER JOIN @last n2 ON n1.ID=n2.ID;

:

NameFirst                      NameLast
------------------------------ ------------------------------
Little                         Timmy
John                           Doe
Baby                           Jessica
+2

, " ". undefined. ! .

. :

SELECT FirstName AS First, LastName AS Last
FROM
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, FirstName
  FROM FirstName
) t1
INNER JOIN
(
  SELECT ROW_NUMBER() OVER (ORDER BY ID) AS r, LastName
  FROM LastName
) t2
ON t1.r = t2.r

"SORT ORDER HERE". . : "ID" "FirstName".

: , ID. .

+1

, :

select t1.id, t1.name, t2.name
from (select name, row_number() over (partition by null order by (select NULL)) as id
      from t1
     ) t1 join
     (select name, row_number() over (partition by null order by (select NULL)) as id
      from t2
     ) t2
     on t1.id = t2.id

, row_number() , , . , .

, ! ! , , .

? , , .

0

@JohnDewey, . , :

DECLARE @first TABLE(sequence INT IDENTITY(1,1), FirstName VARCHAR(30));
DECLARE @last TABLE(sequence INT IDENTITY(1,1), LastName VARCHAR(30));

INSERT INTO @first(FirstName)
SELECT FirstName FROM TableFisrt ORDER BY id;
-- sequence FirstName
-- ======== =========
--        1  Little
--        2  John  
--        3  Baby 

INSERT INTO @last(LastName)
SELECT FirstName FROM TableLast ORDER BY id;
-- sequence LastName
-- ======== =========
--        1  Timmy
--        2  Doe
--        3  Jessica 

SELECT frs.FirstName, lst.LastName
FROM @first frs
INNER JOIN @last lst ON frs.sequence = lst.sequence;
-- sequence FirstName
-- ======== =========
-- Little   Timmy
-- John     Doe
-- Baby     Jessica
0
source

More articles:


All Articles