Question: By writing the following query 

```
SELECT item_name, YEAR( DATE ) , SUM( item_sold_qty ) 
FROM item
JOIN sales ON item.id = sales.item_number
GROUP BY YEAR( DATE ) , item_name
ORDER BY item_name

```

i'm able to get the following result

```
item_name   YEAR( DATE )    SUM( item_sold_qty )
pencil          2011              22
pencil          2012              44
eraser          2012              22
eraser          2011              11
pen             2012              66
pen             2011              33
nib             2012              88
nib             2011              44

```

Instead i want the result in the following way 

```
 item_name      2011    2012
    pencil       22      44            
    eraser       11      22                   
    pen          33      66                  
    nib          44      88

```

I'm not really good at sql and have no clue for how to set the years as column names.
Need help.

**NOTE** :: My database has 2 tables. Sales table has date column which has different dates like 2012-03-01, 2012-04-02, 2011-07-03, so on...

Answer: 
Maybe something like this:

```
SELECT 
    item_name, 
    SUM(CASE WHEN YEAR( DATE )=2011 THEN item_sold_qty ELSE 0 END) AS '2011',
    SUM(CASE WHEN YEAR( DATE )=2012 THEN item_sold_qty ELSE 0 END) AS '2012'
FROM 
    item
JOIN sales ON item.id = sales.item_number
GROUP BY
    item_name
ORDER BY 
    item_name

```

**EDIT**

If you want the other years and still sum them. Then you can do this:

```
SELECT 
    item_name, 
    SUM(CASE WHEN YEAR( DATE )=2011 THEN item_sold_qty ELSE 0 END) AS '2011',
    SUM(CASE WHEN YEAR( DATE )=2012 THEN item_sold_qty ELSE 0 END) AS '2012',
    SUM(CASE WHEN NOT YEAR( DATE ) IN (2011,2012) THEN item_sold_qty ELSE 0 END) AS 'AllOtherYears'
FROM 
    item
JOIN sales ON item.id = sales.item_number
GROUP BY
    item_name
ORDER BY 
    item_name

```

**EDIT2**

If you have a lot of years and you do not want to keep on adding years. Then you need to using dynamic sql. That means that you concat a varchar of the sql and then execute it.

Useful References:

* [MySQL pivot table with dynamic headers based on single column data](https://stackoverflow.com/questions/6158230/mysql-pivot-table-with-dynamic-headers-based-on-single-column-data)
* [How To have Dynamic SQL in MySQL Stored Procedure](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure)
* [MySQL/Pivot table](http://en.wikibooks.org/wiki/MySQL/Pivot_table)
* [MYSQL - Rows to Columns](https://stackoverflow.com/questions/1241178/mysql-rows-to-columns)
Here:

```
SELECT
    i.item_name, 
    SUM(s1.item_sold_qty) AS '2011',
    SUM(s2.item_sold_qty) AS '2012'

FROM item i
     LEFT JOIN sales s1 ON (i.id = s1.item_number AND YEAR(s1.DATE) = 2011)
     LEFT JOIN sales s2 ON (i.id = s2.item_number AND YEAR(s2.DATE) = 2012)

GROUP BY i.item_name
ORDER BY i.item_name

```

Of course, you will have to add as many joins as many years you want to search through...