query for horizontal layout of mysql data

Question

I have a table (delvery_dates) with the following fields:

del_id, del_date, del_ProductID

My normal query produces

    2014-08-23 | 25
    2014-08-23 | 32
    2014-08-23 | 14
    2014-08-23 | 15
    2014-08-23 | 56
    2014-08-23 | 34
    2014-08-27 | 32
    2014-08-27 | 11
    2014-08-27 | 19
    2014-08-27 | 35

etc

I would like a query that outputs in the following format:

    del_date, del_ProductID-1, del_ProductID-2, del_ProductID-3, del_ProductID-4 .. up to 6
    2014-08-23    25                 32                14             15
    2014-08-27    32                 11                19             35

I've seen somewhere about pivot tables, but I don't understand!

Any help much appreciated

thanks Chris

Answer 1

What you need is a Pivot query. Since MySQL does not have a statement for that, you'll need to write it "by hand" (more exactly, create a dynamic SQL expression):

So, it may be something like this:

-- First you need to build the column list.
-- The "CASE ... END" expression will filter the data for each column
-- I use "max()" as an example; use whatever aggregate function you need.
select
  group_concat(distinct
    concat(
      'max(case when del_ProductID = ', del_productID, ' then del_id end) ',
      'as `del_productID-', del_productID, '` '
    )
  )
into @sql
from example;

-- Now build the full SELECT statement
set @sql = concat('SELECT del_date, ', @sql, ' from example group by del_date');


-- OPTIONAL: Check the SELECT statement you've just built
select @sql;

-- Prepare a statement using the SELECT statement built above
prepare stmt from @sql;
execute stmt;

-- When you are done, be sure to dealocate the prepared statement
deallocate prepare stmt;

Please see this example in SQL fiddle.

---

The explanation

You may say "dude, this looks quite complex!"... but it's not complex at all (it's just laborious). So, how does the above solution works?

The first step is to build the column list and an expression to fill it. The group_concat() function will take row values (or expressions) and concatenate them, separating them by commas. You need an aggregate function to show the values in the result of the pivot table. I chose max() as an example, but you can use sum(), average() or any other aggregate function.

As for the case ... end piece inside the aggregate function, you need that each column of the pivot table matches the value of del_productID, so, for example, case when del_ProductID = 1 then del_id end will return the value of del_id only if del_ProductID is 1 (will return null in any other case, you can add else 0 if you want to return zero, for example).

The select ... into will store the result of the expression into a variable called @sql.

After you've built the column list, you need to write the rest of the select statement... that's done with the concat() function.

As for the rest, it's pretty straight forward: @sql is a string, so if you want to execute it, you need to create a prepared statement using its value (which is a select statement), and execute it.