T-SQL:水平连接结果集

问题描述

我有两个表，每个表都将自己的结果集作为一行生成.我想将这些结果集合并为一行.例如:

I have two tables, each which produce their own result-sets as a single row. I would like to join these result-sets into one single row. For example:

SELECT *
FROM Table 1
WHERE Year = 2012 AND Quarter = 1

结果:

Year  Quarter  Name  State  Mail
2012  1        Bob   NY     bob@gmail

查询 #2:

SELECT *
FROM Table 2
WHERE Year = 2012 AND Quarter = 1

结果:

Year  Quarter  Name  State  Mail
2012  1        Greg  DC     greg@gmail

期望的结果集:

SELECT *
FROM Table 3
WHERE Year = 2012 AND Quarter = 1

Year  Quarter  T1Name  T1State  T1Mail     T2Name  T2State  T2Mail
2012  1        Bob     NY       bob@gmail  Greg    DC       greg@gmail

结果被加入/旋转到 Year 和 Quarter 的组合上，这将通过参数输入到查询中.任何帮助将不胜感激.提前致谢！

The results are joined/pivoted onto the combination of Year and Quarter, which will be fed into the query via parameters. Any assistance would be greatly appreciated. Thanks in advance!

推荐答案

除非我遗漏了什么，看来你可以加入 year/quarter 似乎没有必要对数据进行透视:

Unless I am missing something, it looks like you can just join the tables on the year/quarter there doesn't seem to be a need to pivot the data:

select t1.year, 
  t1.quarter,
  t1.name t1Name,
  t1.state t1State,
  t1.mail t1Mail,
  t2.name t2Name,
  t2.state t2State,
  t2.mail t2Mail
from table1 t1
inner join table2 t2
  on t1.year = t2.year
  and t1.quarter = t2.quarter
where t1.year = 2012
  and t1.quarter = 1;

参见SQL Fiddle with Demo

现在如果有关于 year 和 quarter 是否存在于两个表中的问题，那么您可以使用 FULL OUTER JOIN:

select coalesce(t1.year, t2.year) year, 
  coalesce(t1.quarter, t2.quarter) quarter,
  t1.name t1Name,
  t1.state t1State,
  t1.mail t1Mail,
  t2.name t2Name,
  t2.state t2State,
  t2.mail t2Mail
from table1 t1
full outer join table2 t2
  on t1.year = t2.year
  and t1.quarter = t2.quarter
where (t1.year = 2012 and t1.quarter = 2)
  or  (t2.year = 2012 and t2.quarter = 2)