Bellow is the code:

-- getting required columns to a temporary table

SELECT LTRIM(RTRIM(Zip)) Zip, Code ,LTRIM(RTRIM(City)) CLLI, LTRIM(RTRIM(City)) CLLI,LTRIM(RTRIM(State)) State,Processed ,LTRIM(RTRIM(A))[A],LTRIM(RTRIM())

,LTRIM(RTRIM([C]))[C],LTRIM(RTRIM([D]))[D],LTRIM(RTRIM([E]))[E],LTRIM(RTRIM([F]))[F],LTRIM(RTRIM([G]))[G],LTRIM(RTRIM(H))H ,LTRIM(RTRIM(I)) I

INTO #IMS_StgTemp FROM IMS_StgUpload WHERE (Processed IS NULL OR Processed ='D')

CREATE NONCLUSTERED INDEX [IDXRT] ON #IMS_ StgTemp

(

[Zip] ASC,

City ASC,

[A] ASC,

ASC,

[C] ASC,

[D] ASC,

[E] ASC,

[F] ASC,

[G] ASC,

[H] ASC,

ASC,

[State] ASC,

[Processed] ASC,

[Code] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

INSERT INTO #REPORT_ZIP (Zip, Code,State, Totals)

SELECT LTRIM(RTRIM(Zip)), ISNULL(Code,'') LEAD_OWNER, ISNULL( LTRIM(RTRIM(State)),'') STATE, count(*)

FROM #IMS_ StgTemp

WHERE (Processed IS NULL OR Processed ='D')

GROUP BY LTRIM(RTRIM(Zip)) , ISNULL(Code,''), ISNULL(LTRIM(RTRIM(State)),'')

ORDER BY LTRIM(RTRIM(Zip)) , ISNULL(Code,''), ISNULL(LTRIM(RTRIM(State)),'')

UPDATE t

SET t.CLITY= C.CityList

, t.Town= C.townList

,t.SharedBy =C.SharedOwnerList

, t.I = C.I

FROM #B2BICLREPORT_ZIP t INNER JOIN

(SELECT p.zip, ISNULL(p.OfficeCode,'')OfficeCode

,stuff((SELECT DISTINCT ', ' + LTRIM(RTRIM(CLLI)) FROM #IMS_StgB2BLeadsUpload LC

where (Processed IS NULL OR Processed ='DINP') AND LTRIM(RTRIM(LC. Zip)) = p.Zip

and ISNULL(LC. Code,'') = ISNULL(p.Code,'') for xml path('')),1,2,'') as ClliList

,stuff((SELECT DISTINCT ', ' + LTRIM(RTRIM(City)) FROM #IMS_StgB2BLeadsUpload LC

where (Processed IS NULL OR Processed ='DINP') and LTRIM(RTRIM(LC.Zip)) = p.Zip

and ISNULL(LC. Code,'') = ISNULL(p.Code,'') for xml path('')),1,2,'') as CityiList

,stuff((SELECT DISTINCT ', ' + LTRIM(RTRIM(Code)) FROM #IMS_StgB2BLeadsUpload LC

where (Processed IS NULL OR Processed ='DINP') and LTRIM(RTRIM(LC. Zip)) = p.Zip

and ISNULL(LC. Code,'') != ISNULL(p. Code,'') for xml path('')),1,2,'') as SharedOwnerList

, (select count(*) FROM #IMS_StgB2BLeadsUpload LC

where (Processed IS NULL OR Processed ='DINP') and LTRIM(RTRIM(LC.SrvcZip)) = p.Zip

and ISNULL(LC. Code,'') != ISNULL(p. Code,'')) I

FROM #B2BICLREPORT_ZIP p

GROUP BY p.Zip, ISNULL(p.Code,''),CLLI, City, Code) C

ON C. Zip = t.Zip and C. Code = ISNULL(t.Code,'')