summarizing two tables into one table
-
Hello! All!
I was trying to summarize two tables into one table, but could not think of a way of doing it. I wonder if it is possible to do it. The following is an example of my case. I think it would be better to show you the example.
t1
--------------
name Feb
--------------
aaa 12
bbb 16
ccc 19
t2
-----------------
name March
-----------------
aaa 100
ccc 200
ddd 400
The result I wanted to get is:
-------------------------
name Feb March
-------------------------
aaa 12 100
bbb 16
ccc 19 200
ddd 400
Actually, I do not think it can be done on SQL Server. If you have any suggestion, please give me a hand. Thank you!!!!
-
I just figired it out. The answer is:
select a.[name], a.Feb, b.March
from TABLE1 a left join TABLE2 b on a.[name] = b.[name]
union
select a.[name], b.Feb, a.March
from TABLE2 a left join TABLE1 b on a.[name] = b.[name]
Quite easy, I just didn't think of this solution!
-
Hi wenny,
you can also use a full outer join like this
SELECT distinct CASE ISNULL( t2.[name],'')
When '' THEN t1.[name]
else t2.name
end AS name, t1.feb, t2.March
FROM t1 full OUTER join
t2 on t1.name = t2.name
you should compare speed if the two tables are large.
Cheers,
John R. Hanson
-
Hi, John:
Thank you for sharing the answer and the advise. I will try it!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply