sql query to set year as column name

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 1

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
• How To have Dynamic SQL in MySQL Stored Procedure
• MySQL/Pivot table
• MYSQL - Rows to Columns