假设我的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

我能做的是,我可以建立一个tablevariable并指定所有可能的列,并将每个select语句中的记录插入其中。但是,也许像PIVOT这样更好的解决方案?
编辑

查询:1st:Select CategoryId as Id, Animal, AnimalId from Animal第二名: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

SQL Fiddle with Demo

如果在同一行中不需要它们,请使用UNION ALL:
select id, aname, aid, 'Animal' tbl
from animal
union all
select id, tname, tid, 'Transport'
from transport

SQL Fiddle with Demo

编辑#1,这是一个带有UNPIVOTPIVOT的版本:
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

SQL Fiddle with Demo

关于sql - 水平合并选择语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12395761/

10-16 08:56
查看更多