If you have maximum one comment per (Dealer, Year, Month, Car_Id) then you can enumerate the rows and then use a FULL OUTER JOIN.
SET NOCOUNT ON;
USE tempdb;
GO
DECLARE @T1 TABLE (
Camry_Id int,
[Month] tinyint,
[Year] smallint,
Dealer varchar(25),
Camry_Comments varchar(50)
);
DECLARE @T2 TABLE (
Corolla_Id int,
[Month] tinyint,
[Year] smallint,
Dealer varchar(25),
Corolla_Comments varchar(50)
);
INSERT INTO @T1 (
Camry_Id,
[Month],
[Year],
Dealer,
Camry_Comments
)
VALUES
(1,7,2013,'Hendrick','Camry Comment 1'),
(2,7,2013,'Hendrick','Camry Comment 2'),
(3,7,2013,'Hendrick','Camry Comment 3'),
(4,7,2013,'AutoCity','Camry Comment 4'),
(5,7,2013,'AutoCity','Camry Comment 5'),
(6,7,2013,'Leith','Camry Comment 6'),
(7,8,2013,'Leith','Camry Comment 8'),
(8,8,2013,'Leith','Camry Comment 9');
INSERT INTO @T2 (
Corolla_Id,
[Month],
[Year],
Dealer,
Corolla_Comments
)
VALUES
(1,7,2013,'AutoCity','Corolla Comment 1'),
(2,7,2013,'AutoCity','Corolla Comment 2'),
(3,7,2013,'AutoCity','Corolla Comment 6'),
(4,7,2013,'Leith','Corolla Comment 3'),
(4,7,2013,'Leith','Corolla Comment 8'),
(6,8,2013,'Leith','Corolla Comment 4'),
(8,7,2013,'Hendrick','Corolla Comment 7');
WITH P AS (
SELECT
*, ROW_NUMBER() OVER(PARTITION BY Dealer, [Year], [Month] ORDER BY Camry_Id) AS rn
FROM
@T1
),
Q AS (
SELECT
*, ROW_NUMBER() OVER(PARTITION BY Dealer, [Year], [Month] ORDER BY Corolla_Id) AS rn
FROM
@T2
)
SELECT
COALESCE(P.Dealer, Q.Dealer) AS D,
COALESCE(P.[Year], Q.[Year]) AS Y,
COALESCE(P.[Month], Q.[Month]) AS M,
P.Camry_Comments,
Q.Corolla_Comments
FROM
P
FULL OUTER JOIN
Q
ON P.Dealer = Q.Dealer
AND P.Year = Q.Year
AND P.Month = Q.Month
AND P.rn = Q.rn
ORDER BY
D, Y, M;
GO
BTW, next time post table schema and sample data in the form of "insert" statements. This way we do not need to guess column names, data types, constraints, etc.
Help us to be able to help you!