假设我的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,这是一个带有
UNPIVOT
和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
见SQL Fiddle with Demo
关于sql - 水平合并选择语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12395761/