T-SQL: Joining result-sets horizontally

问题:

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

Results:

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

Query #2:

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

Results:

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

Desired result-set:

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

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!

回答1:

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;

See SQL Fiddle with Demo

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)

See SQL Fiddle with Demo

回答2:

All you need to do is to determine the join criteria, i.e. the columns that have to have the matching values in both tables. In your case it looks to be Year and Quarter
so you would write something like select * from Table_1 A Join Table_2 B ON A.year=B.year and A.quarter=B.quarter

回答3:

If you have two queries that return one row, you can put them in one result row using cross join:

select t1.Year, t1.Quarter, T1.Name, T1.State, T1.Mail, T2.Name, T2.State, T2.Mail
from (SELECT *
      FROM Table 1
      WHERE Year = 2012 AND Quarter = 1
     ) t1 cross join
     (SELECT *
      FROM Table 2
      WHERE Year = 2012 AND Quarter = 1
     ) t2

If you want them in separate rows, then you use union all (assuming they have the same columns in the same positions).