CODEWITHSUNDEEP
mysqlsql
Sid
Sid

Reputation: 765

SQL Select to spread row data to columns

I have this select

SELECT  
        products.prod_name, SUM(beg_inv - end_inv)  AS inv,
        MONTH(task_date) MONTH
    FROM    performances 
        INNER JOIN tasks 
        ON performances.task_id = tasks.task_id
        INNER JOIN people
        ON people.person_id = tasks.emp_id
        INNER JOIN products
        ON performances.prod_id = products.prod_id
    WHERE   YEAR(task_date) = '2013'
    GROUP BY performances.prod_id, MONTH(task_date)
    ORDER BY MONTH(task_date), prod_name

and its out put is:

Prod name   inv month
Product 1   5   3
Product 1   10  5

I would want to spread it like this:

Product Name    January February    March   April   May June    July    August  September   October November    December
Product 1                               5           10

Is it possible without having to do a Pivot and just in select?

Upvotes: 2

Views: 8496

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Just do the pivot in SQL like this:

SELECT products.prod_name,
       SUM(case when MONTH(task_date) = 1 then beg_inv - end_inv end)  AS January,
       SUM(case when MONTH(task_date) = 2 then beg_inv - end_inv end)  AS February,
       SUM(case when MONTH(task_date) = 3 then beg_inv - end_inv end)  AS March,
       . . .  
FROM    performances 
        INNER JOIN tasks 
        ON performances.task_id = tasks.task_id
        INNER JOIN people
        ON people.person_id = tasks.emp_id
        INNER JOIN products
        ON performances.prod_id = products.prod_id
WHERE   YEAR(task_date) = 2013
GROUP BY products.prod_name,
ORDER BY products.prod_name,;

Note that this also requires small changes to the GROUP BY and ORDER BY clauses.

Upvotes: 3

Related Questions