SQLServer: How to pair up ordered data for sequences of arbitrary (and unequal length)?

Question

Here's my scenario: I have two tables A, B which (for the sake of this question are identical):

Table X (PK)

ID 
1
2

Table A:

ID FKID Value Sort
1  1    a     1
2  1    aa    2
3  1    aaa   3
4  2    aaaa  1
5  2    aaaaa 2

Table B:

ID FKID Value Sort
1  1    b     1
2  1    bb    2
3  2    bbb   1
4  2    bbbb  2
5  2    bbbbb 3

Desired Output:

FKID ValueA ValueB Sort
1    a      b      1
1    aa     bb     2
1    aaa    (null) 3
2    aaaa   bbb    1
2    aaaaa  bbbb   2
2    (null) bbbbb  3

So record 1 has 3-As and 2-Bs and record 2 has 2-As and 3-Bs all nicely paired up by the Sort integer column.

My current solution involves cross joining with a Numbers table. It works but since the number of items in these tables is unbounded my numbers table is largish (the application is theorhetically unbounded but practically, I can limit it to 1000).

I could also generate the numbers table with a function and a subquery but that feels even worse for performance (I know, I need to test it!).

So I was thinking: perhaps there's a better way to approach this problem? I'm hoping for a happy medium between where I am now and merging the tables together.

One more thing: I'm stuck on SQL Server 2000 :P.

Update: Added PK table above to clarify what I was looking for. I also fixed the desired output. Sorry about that.

Update: Complete solution:

DECLARE @X AS TABLE (ID INT)
DECLARE @A AS TABLE (ID INT, FKID INT, Value VARCHAR(10), Sort INT)
DECLARE @B AS TABLE (ID INT, FKID INT, Value VARCHAR(10), Sort INT)

INSERT INTO @X (ID) VALUES (1)
INSERT INTO @X (ID) VALUES (2)

INSERT INTO @A (ID, FKID, Value, Sort) VALUES (1, 1, 'a',     1)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (2, 1, 'aa',    2)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (3, 1, 'aaa',   3)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (4, 2, 'aaaa',  1)
INSERT INTO @A (ID, FKID, Value, Sort) VALUES (5, 2, 'aaaaa', 2)

INSERT INTO @B (ID, FKID, Value, Sort) VALUES (1, 1, 'b',     1)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (2, 1, 'bb',    2)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (3, 2, 'bbb',   1)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (4, 2, 'bbbb',  2)
INSERT INTO @B (ID, FKID, Value, Sort) VALUES (5, 2, 'bbbbb', 3)

SELECT * FROM @X
SELECT * FROM @A
SELECT * FROM @B

SELECT COALESCE(A.FKID, B.FKID) ID
  ,A.Value
  ,B.Value
  ,COALESCE(A.Sort, B.Sort) Sort
FROM @X X
LEFT JOIN @A A ON A.FKID = X.ID
FULL OUTER JOIN @B B ON B.FKID = A.FKID AND B.Sort = A.Sort

Answer 1

I'm not 100% clear on what you're after, but Try this and see if it is what you want

Select Coalesce(a.FKID, b.FKID) FKID,
    a.Value, B.Value, 
    Coalesce(a.Sort, b.Sort) Sort
From TableA a Full Join TableB b
    On a.Sort = b.sort
       And Left(a.value,1) = 'a'
       And Left(b.value,1) = 'b'
       And Len(a.value) = Len(b.value)

Answer 2

SELECT A.FKID,  A.Value AS ValueA, B.Value AS ValueB, A.Sort
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID
UNION 
SELECT B.FKID,  A.Value AS ValueA, B.Value AS ValueB, B.Sort
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID

Note: This will return duplicate records for a match of ID & FKID (where Sort differs). If you remove the Sort field from the query, you will get the results, you are looking for.

SELECT A.FKID,  A.Value AS ValueA, B.Value AS ValueB, A.Sort AS ASort, 
B.Sort AS BSort
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID
UNION 
SELECT B.FKID,  A.Value AS ValueA, B.Value AS ValueB,
A.Sort AS ASort, B.Sort AS BSort
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.ID = B.ID AND A.FKID = B.FKID

Answer 3

select COALESCE(tt1.FKID, tt2.FKID) FKID, 
       tt1.Value ValueA, 
       tt2.Value ValueB,
       CASE WHEN tt1.Sort IS NULL OR tt2.Sort IS NULL
            THEN COALESCE(tt1.Sort, tt2.Sort)
            ELSE CASE WHEN tt1.Sort >= tt2.Sort 
                      THEN tt1.Sort 
                      ELSE tt2.Sort
                 END
       END Sort 
from tt1
full join tt2 on tt1.FKID = tt2.FKID and len(tt1.value) = len(tt2.value)
order by COALESCE(tt1.FKID, tt2.FKID)

Answer 4

select 
    coalesce(a.fkid, b.fkid) fkid, 
    A.Value as ValueA, 
    B.Value as ValueB, 
    coalesce(a.sort, b.sort) Sort
from a full outer join b
     on a.fkid = b.fkid
     and a.sort = b.sort
order by fkid, sort