question

somdeepgniyogi avatar image
somdeepgniyogi asked

Sql Query Help

![alt text][1] [1]: /storage/temp/2603-capture.png
sqlpivot
capture.png (11.8 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sabinweb avatar image
sabinweb answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

somdeepgniyogi avatar image
somdeepgniyogi answered
Thanks a lot...It works...
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

capture.png (35.0 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sabinweb avatar image sabinweb commented ·
Maybe you should create a new thread/post.
0 Likes 0 ·
sabinweb avatar image
sabinweb answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.