問題描述
我有兩個表，每個表都將自己的結果集作為一行生成.我想將這些結果集合并為一行.例如:
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:
Now if there is a question on whether or not the year and quarter will exist in both tables, then you could use a 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)
參見SQL Fiddle with Demo
這篇關于T-SQL:水平連接結果集的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！