I have a table with 3 columns: ItemCode, Quantity and DocDate. I would like to create the following report in a more "elegant" way:
SELECT T0.ItemCode,
(SELECT SUM(QUANTITY) FROM MyTable T1 WHERE YEAR(T0.DocDate) = 2011 AND T0.ItemCode = T1.ItemCode) AS '2011',
(SELECT SUM(QUANTITY) FROM MyTable T1 WHERE YEAR(T0.DocDate) = 2012 AND T0.ItemCode = T1.ItemCode) AS '2012'
FROM MyTable T0
GROUP BY T0.ItemCode, YEAR(T0.DocDate)
I'm pretty sure there's a better, more efficient way to write this but I can't come up with the right syntax. Any ideas?
You can try this:
SELECT T0.ItemCode,
SUM(CASE WHEN YEAR(T0.DocDate) = 2011 THEN QUANTITY ELSE 0 END) AS '2011',
SUM(CASE WHEN YEAR(T0.DocDate) = 2012 THEN QUANTITY ELSE 0 END) AS '2012'
FROM MyTable T0
GROUP BY
T0.ItemCode
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With