Pivot SQL Text columns

Hi Zaik93,

According to your merge logic, the row with “Corolla Comment 8” in table 2 should map to the row with “Camry Comment 6” in table 1, I made an example, you can refer to the codes:

declare @Table1 table
(
 Camry_Id int,
 [Month] int,
 [Year] int,
 Dealer varchar(10),
 Camry_Comments varchar(20)
)
insert into @Table1 (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')
declare @Table2 table
(
 Camry_Id int,
 [Month] int,
 [Year] int,
 Dealer varchar(10),
 Corolla_Comments varchar(20)
)
insert into @Table2 (Camry_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 cte1 as
(
 select [month],[year],dealer,Camry_Comments,ROW_NUMBER() over(partition by dealer,[month] order by Camry_Comments) as RowNum
 from @Table1
)
,cte2 as
(
 select [month],[year],dealer,Corolla_Comments,ROW_NUMBER() over(partition by dealer,[month] order by Corolla_Comments) as RowNum
 from 
 (
  select [month],[year],dealer,Corolla_Comments,ROW_NUMBER() over(partition by Camry_Id order by Corolla_Comments desc) rownum2
  from @Table2
 ) as x
 where rownum2=1
) 
--select *
-- from cte1 t1
--full join cte2 t2
--on t1.Dealer = t2.Dealer
--and t1.[Month] = t2.[Month]
--and t1.[Year] = t2.[Year]
--and t1.RowNum = t2.RowNum
,cte3 as
(
select case when t1.[Month] is null then t2.Month else t1.[Month] end as [Month]
,case when t1.[year] is null then t2.[Year] else t1.[year] end as [Year]
,case when t1.Dealer is null then t2.Dealer else t1.dealer end as  Dealer
,case when t1.[Camry_Comments] is null then t2.[Corolla_Comments] else t1.[Camry_Comments] end as [Camry_Comments]
, case when t1.[Camry_Comments] is not null then t2.[Corolla_Comments] else t1.[Camry_Comments] end as [Corolla_Comments]
 from cte1 t1
full join cte2 t2
on t1.Dealer = t2.Dealer
and t1.[Month] = t2.[Month]
and t1.[Year] = t2.[Year]
and t1.RowNum = t2.RowNum
)
select * from cte3;

---

Allen Li

TechNet Community Support