水平联合选择语句

假设我的 select 语句的结果如下（我有 5 个）：

   Id  Animal AnimalId
    1   Dog     Dog1
    1   Cat     Cat57

   Id  Transport TransportId
    2   Car        Car100
    2   Plane      Plane500

我想得到如下结果：

   Id  Animal AnimalId    Transport    TransportId
    1   Dog     Dog1
    1   Cat     Cat57
    2                        Car          Car100
    2                       Plane         Plane500 

我能做的是创建一个表变量并指定所有可能的列并将每个 select 语句中的记录插入其中。但也许像 PIVOT 这样更好的解决方案？
Edit

查询：第一：Select CategoryId as Id, Animal, AnimalId from Animal
2nd: Select CategoryId as Id, Transport, TransportId from Transport

怎么样，如果你需要它们在同一行，这会得到row_number()对于每一行并加入这些行：

select a.id,
  a.aname,
  a.aid,
  t.tname,
  t.tid
from 
(
  select id, aname, aid, row_number() over(order by aid) rn
  from animal
) a
left join
(
  select id, tname, tid, row_number() over(order by tid) rn
  from transport
) t
  on a.rn = t.rn

see SQL 摆弄演示 http://sqlfiddle.com/#!3/f69c1/2

如果您不需要将它们放在同一行，则使用UNION ALL:

select id, aname, aid, 'Animal' tbl
from animal
union all
select id, tname, tid, 'Transport'
from transport

see SQL 摆弄演示 http://sqlfiddle.com/#!3/f69c1/4

编辑#1，这是一个带有UNPIVOT and PIVOT:

select an_id, [aname], [aid], [tname], [tid]
from
(
  select *, row_number() over(partition by col order by col) rn
  from animal
  unpivot
  (
    value
    for col in (aname, aid)
  ) u
  union all
  select *, row_number() over(partition by col order by col) rn
  from transport
  unpivot
  (
    value
    for col in (tname, tid)
  ) u
) x1
pivot
(
  min(value)
  for col in([aname], [aid], [tname], [tid])
) p
order by an_id

see SQL 摆弄演示 http://sqlfiddle.com/#!3/72a8f/31