Complex SELECT statement; one-to-many all details in one row

Question

There are two tables:

Products
ID (Primary Key),
ProductName

PlannedByMonths
ProductID (Primary Key) (Link to the Products table many-to-one),
MonthNumber (Primary Key),
QtytoProduce,

How to write SELECT statement to retrieve results in the following format?

ProductName, QtytoProduceMonth1, QtytoProduceMonth2, QtytoProduceMonth3, QtytoProduceMonth4, QtytoProduceMonth5, QtytoProduceMonth6, QtytoProduceMonth7, QtytoProduceMonth8, QtytoProduceMonth9, QtytoProduceMonth10, QtytoProduceMonth11, QtytoProduceMonth12

Thank you.

Answer 1

I'm still not sure if this isn't the worst technological advancement since TV dinners, but starting in v4.1 MySQL has the GROUP_CONCAT() function which does what you want:

  SELECT p.ProductName
         , group_concat(pbm.QtytoProduce order by pbm.MonthNumber)
    FROM Products p
         INNER JOIN PlannedByMonths pbm
         ON p.ID = pbm.ProductID
GROUP BY p.prodname

This returns one row for each ProductName containing:

1. the ProductName, and
2. the QtytoProduce for each MonthNumber in a BLOB.

Answer 2

You need 12 joins for that:

select ProductName, pbm1.QtytoProduce, pbm2.QtytoProduce, pbm3.QtytoProduce, pbm4.QtytoProduce, pbm5.QtytoProduce, pbm6.QtytoProduce, pbm7.QtytoProduce, pbm8.QtytoProduce, pbm9.QtytoProduce, pbm10.QtytoProduce, pbm11.QtytoProduce, pbm12.QtytoProduce from Products p
left join PlannedByMonths pbm1 on p.ID=pbm1.ProductID and pbm1.MonthNumber=1
left join PlannedByMonths pbm1 on p.ID=pbm2.ProductID and pbm2.MonthNumber=2
left join PlannedByMonths pbm1 on p.ID=pbm3.ProductID and pbm3.MonthNumber=3
left join PlannedByMonths pbm1 on p.ID=pbm4.ProductID and pbm4.MonthNumber=4
left join PlannedByMonths pbm1 on p.ID=pbm5.ProductID and pbm5.MonthNumber=5
left join PlannedByMonths pbm1 on p.ID=pbm6.ProductID and pbm6.MonthNumber=6
left join PlannedByMonths pbm1 on p.ID=pbm7.ProductID and pbm7.MonthNumber=7
left join PlannedByMonths pbm1 on p.ID=pbm8.ProductID and pbm8.MonthNumber=8
left join PlannedByMonths pbm1 on p.ID=pbm9.ProductID and pbm9.MonthNumber=9
left join PlannedByMonths pbm1 on p.ID=pbm10.ProductID and pbm10.MonthNumber=10
left join PlannedByMonths pbm1 on p.ID=pbm11.ProductID and pbm11.MonthNumber=11
left join PlannedByMonths pbm1 on p.ID=pbm12.ProductID and pbm12.MonthNumber=12

Answer 3

I don't know if there's a simpler way to pivot in MySQL, but this should work:

        select 
        (select descriptions from products d where d.productid = p.productid )
           description,
         max(if(month=1,Qty, null)) m1,
         max(if(month=2, Qty,null)) m2,
         max(if(month=3, Qty, null)) m3 
         /* more here */
         from Planned p 
         group by productid  ;

Answer 4

Do not do that.

Put your business logic outside the query. Move your business logic form query to code.

The business logic in query is hard to maintain, hard to extend and gives you small opportunities to manipulate with. If you like complex business logic to put in db layer use stored procedures. Otherwise use code to make the business logic.

Regards