Here is a quick solution, adds a row number to join the two sets

😎

IF OBJECT_ID('Tempdb..#tTable1') IS NOT NULL

DROP TABLE #tTable1

CREATE TABLE #tTable1(ID INT,

Value1 VARCHAR(50),

Value2 VARCHAR(20))

INSERT INTO #tTable1(ID, Value1, Value2)

VALUES

(222,'A','B'),

(222,'R','F'),

(222,'T','C')

IF OBJECT_ID('Tempdb..#tTable2') IS NOT NULL

DROP TABLE #tTable2

CREATE TABLE #tTable2(ID INT,

Value3 VARCHAR(50),

Value4 DATETIME)

INSERT INTO #tTable2(ID, Value3, Value4)

VALUES

(222,'A',GETDATE()),

(222,'A',GETDATE()+3),

(222,'C',GETDATE()-8),

(222,'R',GETDATE()+2),

(222,'T',GETDATE()-5);

;WITH TBL_1 AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value1) AS VALUE_RID

,ID

,Value1

,Value2

FROM #tTable1

)

,TBL_2 AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value3) AS VALUE_RID

,ID

,Value3

,Value4

FROM #tTable2

)

SELECT

T2.ID

,T1.Value1

,T1.Value2

,T2.Value3

,T2.Value4

FROM TBL_2 T2

LEFT OUTER JOIN TBL_1 T1

ON T2.ID = T1.ID

AND T2.VALUE_RID = T1.VALUE_RID;

Results

ID Value1 Value2 Value3 Value4

---- ------- ------- ------- -----------------------

222 A B A 2014-08-28 09:09:43.257

222 R F A 2014-08-31 09:09:43.257

222 T C C 2014-08-20 09:09:43.257

222 NULL NULL R 2014-08-30 09:09:43.257

222 NULL NULL T 2014-08-23 09:09:43.257