| |
| Sales |
| Year |
Item |
Qty |
| 2010 |
Pen |
10 |
| 2010 |
Bag |
20 |
| 2010 |
Cap |
30 |
| 2011 |
Pen |
40 |
| 2011 |
Bag |
50 |
| 2011 |
Cap |
60 |
| 2012 |
Pen |
70 |
| 2012 |
Bag |
80 |
| 2012 |
Cap |
90 |
|
---- for Big Tables
SELECT Year = s.Year,
SUM (CASE Item WHEN 'Pen' THEN Qty ELSE 0 END) as Pen,
SUM (CASE Item WHEN 'Bag' THEN Qty ELSE 0 END) as Bag,
SUM (CASE Item WHEN 'Cap' THEN Qty ELSE 0 END) as Pin
FROM Sales s
GROUP BY Year
---- for Small Tables
SELECT Year,
Pen=ISNULL((SELECT Qty FROM Sales WHERE Item = 'Pen' AND YEAR = s.YEAR),0),
Bag=ISNULL((SELECT Qty FROM Sales WHERE Item = 'Bag' AND YEAR = s.YEAR),0),
Cap=ISNULL((SELECT Qty FROM Sales WHERE Item = 'Cap' AND YEAR = s.YEAR),0)
FROM Sales s
GROUP BY Year
|
| Output |
| Year |
Pen |
Bag |
Cap |
| 2010 |
10 |
20 |
30 |
| 2011 |
40 |
50 |
60 |
| 2012 |
70 |
80 |
90 |
|