有了这些数据。。。。

WITH
a ( id , txt , dsc ) AS (
          SELECT  1  , 1234 , 'one'
UNION ALL SELECT  2  , 0000 , 'two'
)
,
b (id , textid ) AS (
          SELECT 1  , 1234 
)
,
c(id , textid ) AS (
          SELECT 1  ,  0000  
)

你真的不需要另外两张table。。。

SELECT
  MAX(CASE id WHEN 1 THEN dsc END) AS desc1
, MAX(CASE id WHEN 2 THEN dsc END) AS desc2
FROM a                                      
;

结果：

-- out  desc1 | desc2 
-- out -------+-------
-- out  one   | two

通过几个连接，您可以这样做：

SELECT ISNULL(TableC.id,TableB.id),A1.descr,A2.descr
FROM TableB
FULL JOIN TableC
ON TableB.ID = TableC.ID
LEFT JOIN TableA A1
ON A1.Text = TableB.TextID
LEFT JOIN TableA A2
ON A2.Text = TableC.TextID

您可以尝试使用after join pivot，但需要在（[one]、[two]…）中的运算符后指示每个值：

select [one] as one_column ,[two] as two_column from
(
 select A.Desc from A
 left join B
 on A.ID=B.TextID and A.Text=B.TextID
 left join C
 on A.ID=C.ID and A.Text=C.TextID
)X
PIVOT ( max(X.Desc) For X.Desc in ([one],[two]) ) as piv_tab

也许是这样的？

select a1.desc as desc1, a2.desc as desc2 from b, c, a a1, a a2 where b.textid=a1.text and c.textid=a2.text

目前尚不清楚的是b和c之间的可能关系。如果没有任何的，我们就得到了两个表的完全笛卡尔积