SQL Server Select Join?
I have following select results:
Select t1.ID, t1.Value1 from t1 where t1.ID=1
ID Value1 1 val1 1 val2 1 val3 1 val4
Select t2.ID, t2.Value2 from t2 where t2.ID = 1
ID Value2 1 val5 1 val6 1 val7 1 val8
and I want to obtain like:
ID Value1 Value2 1 val1 val5 1 val2 val6 1 val3 val7 1 val4 val8
How can I obtain this result??
I have had to assume a little bit about what you want based on your results but I think the following will work:
;WITH CTE1 AS ( SELECT ID, Value1, ROW_NUMBER() OVER(ORDER BY Value1) [RowNumber] FROM t1 ), CTE2 AS ( SELECT ID, Value2, ROW_NUMBER() OVER(ORDER BY Value2) [RowNumber] FROM t2 ) SELECT COALESCE(CTE1.ID, CTE2.ID) [ID], Value1, Value2 FROM CTE1 FULL JOIN CTE2 ON CTE1.ID = CTE2.ID AND CTE1.RowNumber = CTE2.RowNumber
FULL JOIN could be replaced with LEFT JOIN or INNER JOIN Depending on your exact requirements.