question

DECLARE @vtSample TABLE ( [Company] VARCHAR(50), [Year] SMALLINT, [Amount1] DECIMAL(18,2), [Amount2] DECIMAL(18,2) ) INSERT INTO @vtSample([Company],[Year],[Amount1],[Amount2]) VALUES('abc',2014,30,40), ('abc',2013,40,50), ('def',2014,10,20), ('def',2013,30,10) ;WITH RawTable1 AS ( SELECT Company ,Year,Amount1 FROM @vtSample) ,RawTable2 AS ( SELECT Company ,Year,Amount2 FROM @vtSample) SELECT ISNULL(A.Company,B.Company) AS Company ,Amount1_2014 ,Amount2_2014 ,Amount1_2013 ,Amount2_2013 FROM ( SELECT Company,[2014] AS Amount1_2014,[2013] AS Amount1_2013 FROM RawTable1 PIVOT (SUM([Amount1]) FOR [Year] IN([2014],[2013]))A)A FULL JOIN ( SELECT Company,[2014] AS Amount2_2014,[2013] AS Amount2_2013 FROM RawTable2 PIVOT (SUM([Amount2]) FOR [Year] IN([2014],[2013]))B)B ON A.Company = B.Company and the result is : Company Amount1_2014 Amount2_2014 Amount1_2013 Amount2_2013 abc 30.00 40.00 40.00 50.00 def 10.00 20.00 30.00 10.00

Thanks a lot...It works...

I have one more requirement... Please help![alt text][1] [1]: /storage/temp/2604-capture.png

One question: is only one company ? 'AAA' ? DECLARE @vtResult TABLE ( [Company] VARCHAR(50), [Cluster] SMALLINT, [Desc] VARCHAR(50), [Amount] DECIMAL(18,2)); INSERT INTO @vtResult([Company],[Cluster],[Desc],[Amount]) VALUES ('AAA',110,'AA',456), ('AAA',120,'BB',456), ('AAA',140,'DD',456), ('AAA',160,'FF',456), ('AAA',170,'GG',456) DECLARE @vtReference TABLE( [Cluster] SMALLINT, [Desc] VARCHAR(50)); INSERT INTO @vtReference([Cluster],[Desc]) VALUES (110,'AA'), (120,'BB'), (130,'CC'), (140,'DD'), (150,'EE'), (160,'FF'), (170,'GG'), (180,'HH') SELECT ISNULL([Company],'AAA') AS [Company], ISNULL(RS.[Cluster],RF.[Cluster]) AS [Cluster], ISNULL(RS.[Desc],RF.[Desc]) AS [Desc], ISNULL(RS.[Amount],0) AS [Amount] FROM @vtResult AS RS FULL JOIN @vtReference AS RF ON RS.Cluster = RF.Cluster AND RS.[Desc] = RF.[Desc] ORDER BY [Company],[Cluster],[Desc] output: Company Cluster Desc Amount AAA 110 AA 456.00 AAA 120 BB 456.00 AAA 130 CC 0.00 AAA 140 DD 456.00 AAA 150 EE 0.00 AAA 160 FF 456.00 AAA 170 GG 456.00 AAA 180 HH 0.00