sql query to set year as column name

mysql, sql

Solution

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

Problem

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...

Original source

Related problems