Pivot table with dynamic columns in MySQL

Stratos Provatopoulos

I am currently working as a web developer in Greece for an advertising agency but I consider myself to be an all around technology enthusiast. I like working both front end and back end, so I guess that makes me a full stack developer. You can find me at , Twitter, Linkedin and Facebook

20 Responses

  1. Michael Cole says:

    Hi Great Article

    My only question is that say if you wanted to apply filters so in your example were size would equal small, how would that be done, because you cant just user were size = “s”

  2. strapro says:

    Thanks and sorry for the late reply!

    You can use the HAVING clause to apply filters, so it would be something like this

    SET @sql = CONCAT(‘SELECT p.id
    , p.name
    , p.description, ‘, @sql, ‘
    FROM product p
    LEFT JOIN product_additional AS pa
    ON p.id = pa.id
    GROUP BY p.id
    HAVING size = “S”‘);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  3. Pelay says:

    What could be a possible reason for my values returning in BLOB when using MAX().

  4. Larc says:

    Hello,
    Greetings from Portugal.
    You helped me a lot.
    Thank you very much, keep up the good work.

  5. Fatih says:

    I applied your steps but it gives this error:

    #1172 – Result consisted of more than one row

    Any idea?

  6. amol says:

    Hello,
    thanx, great job…

  7. mboloz says:

    :) thanks for the article, that’s helped me

  8. Bill says:

    :) Great article. Question for you, if you run this and one of your rows of data that will be a column heading has an invalid character like a period, is there a way to escape it to use it?

  9. Tobi says:

    Thanks a Lot !
    I spent a lot of time and solved my problem with your article !
    😀

  10. luciano silva says:

    very cool your post, but as it would be with the dynamic column. eg date.
    follows the example that I need to do.

    Local 01/12/2014 |02/12/2014 |03/12/2014
    Esteira 12.00 | 12.00 | 12.00
    Mesa 10.00 | 6.00 | 6.50
    Balança 13.50 | 8.00 | 9.50
    ponteira 10.00 | 9.00 | 23.00
    import 13.50 | 10.00 | 46.00

  11. Sunny says:

    Export the pivot-table-with-dynamic-columns as CSV.
    I Need to export the following Query As CSV.

    SELECT id,
    name,
    description,

    GROUP_CONCAT(if (DAYS=’2015-01-01′,AMOUNT,NULL)) As ’01-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-02′,AMOUNT,NUll)) As ’02-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-03′,AMOUNT,NUll)) As ’03-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-04′,AMOUNT,NUll)) As ’04-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-05′,AMOUNT,NUll)) As ’05-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-06′,AMOUNT,NUll)) As ’06-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-07′,AMOUNT,NUll)) As ’07-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-08′,AMOUNT,NUll)) As ’08-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-09′,AMOUNT,NUll)) As ’09-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-10′,AMOUNT,NUll)) As ’10-jan’,
    FROM product
    GROUP BY p.id Where DAYS BETWEEN ‘.$FROM.’ AND ‘.$to.’;

    I use this Query to export to csv
    <?php
    ini_set('memory_limit', '-1');
    // Database Connection

    $host="localhost";
    $uname="xxxxxxxx";
    $pass="xxxxxx";
    $database = "xxxx";

    $connection=mysql_connect($host,$uname,$pass);

    echo mysql_error();

    //or die("Database Connection Failed");
    $selectdb=mysql_select_db($database) or die("Database could not be selected");
    $result=mysql_select_db($database)
    or die("database cannot be selected “);

    // Fetch Record from Database

    $output = “”;
    $table = “xxxxxxxxxxxx”; // Enter Your Table Name
    //$from_data = $_POST[‘from’]; //Should be sanitized before use in the query, or use PDO.
    //$to_data = $_POST[‘to’]; //Should be sanitized before use in the query, or use PDO.

    $sql = mysql_query(“SELECT id,
    name,
    description,

    GROUP_CONCAT(if (DAYS=’2015-01-01′,AMOUNT,NULL)) As ’01-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-02′,AMOUNT,NUll)) As ’02-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-03′,AMOUNT,NUll)) As ’03-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-04′,AMOUNT,NUll)) As ’04-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-05′,AMOUNT,NUll)) As ’05-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-06′,AMOUNT,NUll)) As ’06-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-07′,AMOUNT,NUll)) As ’07-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-08′,AMOUNT,NUll)) As ’08-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-09′,AMOUNT,NUll)) As ’09-jan’,
    GROUP_CONCAT(if (DAYS=’2015-01-10′,AMOUNT,NUll)) As ’10-jan’,
    FROM product
    GROUP BY p.id “);

    $columns_total = mysql_num_fields($sql);

    // Get The Field Name

    for ($i = 0; $i < $columns_total; $i++) {
    $heading = mysql_field_name($sql, $i);
    $output .= '"'.$heading.'",';
    }
    $output .="\n";

    // Get Records from the table

    while ($row = mysql_fetch_array($sql)) {
    for ($i = 0; $i

    As I find difficult to add the query manually as days goes I want to make it dynamic.

    prepared statement will be alternative choose .

  12. Any ideas on how to make this work in php using single database call? So far I think we can’t use mysql prepared statements with php

  13. Shamoun Ilyas says:

    Brilliant article! helped a lot with my work. next step, how to make it work with php..

  14. Steve says:

    I’m from postresql world and now learning mysql. Thanks for this, I can get exactly what I want.
    I need to call this query from a dynamic website by PHP.
    I tried to create a view, a stored process and a function that could be called by PHP without success.
    Help with this would be appreciated
    thanks again for sharing this

  15. Bart says:

    and what if you don’t need all de max and group functions? just want this:

    1 2 3 4
    A a1 a2 a3 a4
    B b1 b2 b3 b4
    C c1 c2 c3 c4
    D d1 d2 d3 d4

    to be dynamicaly turned into this, without calling the columns myself (lets face you have 50 rows, you dont want 50 columns to define yourself):

    A B C D
    1 a1 b1 c1 d1
    2 a2 b2 c2 d2
    3 a3 b3 c3 d3
    4 a4 b4 c4 d4

  16. milan says:

    thanks a lot!!!! very-very-very useful :)

  1. May 27, 2014

    […] Pivot a table with dynamic columns in MySQL. This tutorial uses prepared statements to pivot tables, whose extra characteristics are stored in dynamic rows  […]

  2. September 19, 2015

    […] some cases pivoting a table is not desired, instead we need to convert rows to columns and append them to the result […]

  3. January 15, 2016

    […] 后来,参考了Pivot table with dynamic columns in MySQL这个,才写出来的。 […]

Leave a Reply

Your email address will not be published. Required fields are marked *