How to create view that combine multiple row from 2 tables?

I want to create view that combine data from two tables, sample data in each table is like below.

SELECT Command for TableA

SELECT [ID], [Date]开发者_运维问答, [SUM]
FROM TableA

Result

ID    |    Date     |    SUM
1     |    1/1/2010 |     2
1     |    1/2/2010 |     4
3     |    1/3/2010 |     6

SELECT Command for TableB

SELECT [ID], [Date], [SUM]
FROM TableB

Result

ID    |    Date     |    SUM
1     |    1/1/2010 |     5
1     |    2/1/2010 |     3
1     |   31/1/2010 |     2
2     |    1/2/2010 |     20

I want output like below

ID    |    Date     |    SUMA     |     SUMB
1     |    1/1/2010 |     2       |      10
1     |    1/2/2010 |     4       |       0
2     |    1/2/2010 |     0       |      20
3     |    1/3/2010 |     6       |       0

How can I do that on SQL Server 2005?

Date information be vary, as modify in table.

Try this...

SELECT 
 ISNULL(TableA.ID, TableB.ID) ID, 
 ISNULL(TableA.Date, TableB.Date), 
 ISNULL(TableA.Sum,0) SUMA, 
 ISNULL(TableB.Sum, 0) SUMB
FROM 
 TableA FULL OUTER JOIN TableB 
 ON TableA.ID = TableB.ID AND TableA.Date = TableB.Date
ORDER BY 
 ID

A full outer join is what you need because you want to include results from both tables regardless of whether there is a match or not.

I usually union the two queries together and then group them like so:

SELECT ID, [Date], SUM(SUMA) As SUMA, SUM(SUMB) AS SUMB
FROM (
    SELECT ID, [Date], SUMA, 0 AS SUMB
    FROM TableA
    UNION ALL
    SELECT ID, [Date], 0 As SUMA, SUMB
    FROM TableB
    )
GROUP BY ID, [Date]

SELECT 
   ISNULL(a.ID, b.ID) AS ID,
   ISNULL(a.Date, b.Date) AS Date,
   ISNULL(a.SUM, 0) AS SUMA,
   ISNULL(b.SUM, 0) AS SUMB,
FROM 
   TableA AS a
   FULL JOIN
   TableB AS b
   ON a.ID = b.ID
   AND a.Date = b.Date;

It's not obvious how you want to combine the two tables. I think this is what you're after, but can you confirm please?

1. TableA.Date is the most important field; if a given date occurs in TableA then it will be included in the view, but not if it only occurs in TableB.
2. If a date has records in TableA and TableB and the records have a matching ID, they are combined into one row in the view with SUMA being taken from TableA.Sum and SUMB being TableA.Sum * TableB.Sum (e.g. Date: 01/01/2010, ID: 1) (e.g. Date: 01/03/2010 ID: 3).
3. If a date has records in TableA and TableB with different IDs, the view include these records separately without multiplying the Sum values at all (e.g. Date 02/01/2010, ID: 1 and ID: 2)