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

score 6 · Accepted Answer · edited May 23 '17 at 11:58

6

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:

edited May 23 '17 at 11:58

Community

1
1

answered Apr 26 '12 at 09:46

Arion

31,011
10
70
88

and if you have more years ? [unknown number] – Zyku Apr 26 '12 at 09:48
2

Then you need to use dynamic sql. – Arion Apr 26 '12 at 09:49
1

If he was using mssql. A pivot with dynamic sql is the best solution. You can actually do kinda pivot i mysql. If you are interested look here http://en.wikibooks.org/wiki/MySQL/Pivot_table – Arion Apr 26 '12 at 09:53
@Arion thanks for your help. i really dont know that we can write a query in the way you have shown. good to learn something new. But if we have more years then you said that we need to use dynamic sql. What does that exactly mean? And do i need to keep on adding the cases if i have years starting from 2001 to 2010? – Anil Apr 26 '12 at 10:10
Can we use this SELECT '1' AS YEAR(NOW()) or something like that? – user2223377 Jan 12 '21 at 06:49
@user2223377 no you can’t. For that to work you need to use dynamic sql – Arion Jan 12 '21 at 08:04

score 1 · Answer 2 · answered Apr 26 '12 at 09:58

The answer above will work but adding a case for ever year may or may not be suitable. If you're on a PHP platform you could change the layout of your existing array like such

foreach($items as $item)
{
    $item_names[$item[item_name]][$item[year]] += $item[item_sold_qty];
}

This will make an array as follows:

Array
(
    [pencil] => Array
        (
            [2011] => 22
            [2012] => 44
        )

    [eraser] => Array
        (
            [2012] => 22
            [2011] => 11
        )

    [pen] => Array
        (
            [2012] => 66
            [2011] => 33
        )

    [nib] => Array
        (
            [2012] => 88
            [2011] => 44
        )

)

Also change your original query to assign better names to the variables:

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

The above assumes your mysql result array looks something like this:

Array
(
    [0] => Array
        (
            [item_name] => pencil
            [year] => 2011
            [item_sold_qty] => 22
        )

    [1] => Array
        (
            [item_name] => pencil
            [year] => 2012
            [item_sold_qty] => 44
        )

    [2] => Array
        (
            [item_name] => eraser
            [year] => 2012
            [item_sold_qty] => 22
        )

    [3] => Array
        (
            [item_name] => eraser
            [year] => 2011
            [item_sold_qty] => 11
        )

    [4] => Array
        (
            [item_name] => pen
            [year] => 2012
            [item_sold_qty] => 66
        )

    [5] => Array
        (
            [item_name] => pen
            [year] => 2011
            [item_sold_qty] => 33
        )

    [6] => Array
        (
            [item_name] => nib
            [year] => 2012
            [item_sold_qty] => 88
        )

    [7] => Array
        (
            [item_name] => nib
            [year] => 2011
            [item_sold_qty] => 44
        )

)

score 0 · Answer 3 · answered Apr 26 '12 at 10:04

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

sql query to set year as column name

3 Answers3