Dynamic pivot tables (transform rows to columns)

 

Dynamic pivot tables (transform rows to columns)

MySQL pivot table with dynamic headers.

 

Let’s assume we have a table of properties (a properties table) – ‘properties’ (script of its creation is given below), and we need to do data transformation for the report.

=>

As is generally known (as it is known), in MySql there is no function of automatically table transformation. Surely we can use some program (tool), which is able to connect to MySql and execute data transformation. But in this case we want to do it manually, and we have one variant – to write a query which will execute the data rotation in the table.

This query can have such type as:

As you can see – for each value `property_name` we need to do definite actions. It can be easy when the types of properties do not change. But what we should do if the values of properties in the column `property_name` often change or complement with the new ones? In this case we would change the query every time. In this case the algorithm of dynamic construction of this query can help us, this algorithm has to read all possible values of the column `property_name`, and on its basement create a query. The algorithm of query construction is:

As the result will be created the query:

(the query is formatted additionally for readability)

 

Note: the GROUP_CONCAT result length is limited to the value of the group_concat_max_len system variable, which has a default value of 1024. So, if you have large number of columns, then this value can be set higher before using GROUP_CONCAT function, for example:

 

The query is written into variable @sql; now we can execute it with prepared statements:

 

Script of creation and filling of the table:

0 thoughts on “Dynamic pivot tables (transform rows to columns)”

  1. I have found your code of great help, but I have got stuck when I trid to use it slightly differently.

    basically my table looks ike this

    I would like it to turn out to be

    but it turns out to be

    the adjusted code I have used is

    would very much appreciate if you could help me out with this.

  2. Hi, Oskar.

    You should use aggregate function, for example – MAX()

    SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(ticker =…

  3. I have a table 'narvarorapportering' with fields 'amne', 'lektionstillfalle', 'narvarande', 'efternamn'.

    I have replaced tablename and fields in your code with my own like this:

    When trying this I get the following error message:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '36,MAX(IF(lektionstillfalle = 'v 37', narvarande, NULL)) AS v 37,MAX(IF(lektions' at line 1

    Any help on what could be wrong would be really great!

    /Jonas

  4. Jonas,
    If the names of the future columns contains a space, then there is a syntax error.
    try:

  5. I really appreciate you helping me with this and for your quick reply!

    When trying this I get a different error message:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM narvarorapportering GROUP BY efternamn' at line 1

    /Jonas

  6. Jonas,
    show DDL of the narvarorapportering table.

  7. also show result of the query:

  8. SELECT efternamn, MAX(IF(lektionstillfalle = '36b', narvarande, NULL)) AS `36b`,MAX(IF(lektionstillfalle = '45b', narvarande, NULL)) AS `45b`,MAX(IF(lektionstillfalle = 'Vecka 36', narvarande, NULL)) AS `Vecka 36`,MAX(IF(lektionstillfalle = 'v 37', narvarande, NULL)) AS `v 37`,MAX(IF(lektionstillfalle = 'v 37:2', narvarande, NULL)) AS `v 37:2`,MAX(IF(lektionstillfalle = 'v 38', narvarande, NULL)) AS `v 38`,MAX(IF(lektionstillfalle = 'v 38:2', narvarande, NULL)) AS `v 38:2`,MAX(IF(lektionstillfalle = '35', narvarande, NULL)) AS `35`,MAX(IF(lektionstillfalle = 'v 40', narvarande, NULL)) AS `v 40`,MAX(IF(lektionstillfalle = 'v 40:2', narvarande, NULL)) AS `v 40:2`,MAX(IF(lektionstillfalle = 'v 41', narvarande, NULL)) AS `v 41`,MAX(IF(lektionstillfalle = 'v 41:2', narvarande, NULL)) AS `v 41:2`,MAX(IF(lektionstillfalle = 'v 2', narvarande, NULL)) AS `v 2`,MAX(IF(lektionstillfalle = 'v 42', narvarande, NULL)) AS `v 42`,MAX(IF(lektionstillfalle = 'v 42:2', narvarande, NULL)) AS `v 42:2`,MAX(IF(lektionstillfalle = 'v 45', narvarande, FROM narvarorapportering GROUP BY efternamn

  9. Increase the variable group_concat_max_len
    for example:

  10. When I try as below I get no error message. The query is executed correctly.
    However, it comes back empty. The query gives back 0 rows.
    Again, thanks for your help!

    SET group_concat_max_len=5000;
    SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(lektionstillfalle = ''',
    lektionstillfalle,
    ''', narvarande, NULL)) AS ',
    CONCAT("`",lektionstillfalle,"`")
    )
    ) INTO @sql
    FROM
    narvarorapportering;
    SET @sql = CONCAT('SELECT efternamn, ', @sql, ' FROM narvarorapportering GROUP BY efternamn');

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

  11. I am fairly new to mySQL (previously used ms-sql). I think I am having the same problem as Jonas – when I run the query, it says it was executed successfully, and the output ends with this feedback:

    EXECUTE stmt;# Rows: 8
    DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

    But the 8 rows that supposedly were affected are not displayed. Is there something I'm missing?

  12. tkbell,
    to reproduce the issue show DDL of the table and a few rows of data as insert.

    Jonas,
    Could you show some records of the table.

  13. The query I am using references 2 tables: cms_items and cms_headers. Below is the structure and some data for each and then the query I used, with the results.

    — Table structure for table `cms_items`
    CREATE TABLE `cms_items` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `hid` int(11) NOT NULL,
    `iid` int(11) NOT NULL DEFAULT '0',
    `descript` text NOT NULL,
    `order` tinyint(4) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `hid` (`hid`),
    KEY `iid` (`iid`),
    KEY `order` (`order`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

    INSERT INTO `cms_items` (`id`, `hid`, `iid`, `descript`, `order`) VALUES
    (2, 1, 0, 'Robust, 45 screw terminal', 2),
    (3, 1, 0, 'Reversible door flips, to conveniently show points list', 3),
    (4, 2, 0, 'Universal ranges', 1),
    (5, 3, 0, 'Outputs 1-4 are wired to unpopulated sockets', 4);

    — Table structure for table `cms_headers`
    CREATE TABLE `cms_headers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` int(11) NOT NULL,
    `descript` text NOT NULL,
    `order` tinyint(4) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `type` (`sid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

    INSERT INTO `cms_headers` (`id`, `sid`, `descript`, `order`) VALUES
    (1, 1, 'Superior Design', 1),
    (2, 1, '12 Inputs', 2),
    (3, 1, '8 Outputs', 0);

    — Pivot Query (with results)
    SET @@group_concat_max_len = 5000;# MySQL returned an empty result set (i.e. zero rows).

    SET @sql = NULL;# MySQL returned an empty result set (i.e. zero rows).

    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(cms_headers.descript = ''', cms_headers.descript,''', cms_items.descript, NULL)) AS ',
    CONCAT("`",cms_headers.descript,"`")
    )
    ) INTO @sql
    FROM cms_headers join cms_items on cms_items.hid = cms_headers.id;# Affected rows: 1

    SET @sql = CONCAT('SELECT cms_headers.descript, ', @sql, ' FROM cms_headers join cms_items on cms_items.hid = cms_headers.id GROUP BY descript');# MySQL returned an empty result set (i.e. zero rows).

    PREPARE stmt FROM @sql;# MySQL returned an empty result set (i.e. zero rows).

    EXECUTE stmt;# Rows: 8

    DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

  14. If you are using the phpMyAdmin as mysql client, try execute query without last statment.

    Quote: tkbell
    DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

    P.s.

    EXECUTE stmt;# Rows: 8


    it means that the query returned 8 rows. Unfortunately phpMyAdmin displays latest result of the last statment, which is (i.e. zero rows).

  15. Here are some records from my table:

    INSERT INTO `narvarorapportering` (`IDnarvrap`, `larare`, `amne`, `lektionstillfalle`, `narvarande`, `fornamn`, `efternamn`, `langd`) VALUES
    (384, 'Kristina Ugelstad', 'Kammarensemble', '36b', '', 'Magdalena', 'karlsson', '01:20:00'),
    (416, 'Kristina Ugelstad', 'Mentorstid', 'Vecka 36', 'ja', 'David', 'Storm', '00:40:00'),
    (420, 'Kristina Ugelstad', 'Mentorstid', 'v 37', 'ja', 'Edith', 'Bremer', '00:40:00'),
    (422, 'Kristina Ugelstad', 'Mentorstid', 'v 37', 'ja', 'Asim', 'Mayindu', '00:40:00'),
    (424, 'Kristina Ugelstad', 'Mentorstid', 'v 37', '', 'David', 'Storm', '00:40:00'),
    (479, 'Johan Jrnland', 'Gemensam lektion', 'v 38', 'ja', 'Ardalan', 'Azadi', '01:00:00'),
    (481, 'Johan Jrnland', 'Gemensam lektion', 'v 38', 'ja', 'Edvin', 'Diskin Wetter', '01:00:00'),
    (483, 'Johan Jrnland', 'Gemensam lektion', 'v 38', '', 'Gursel', 'Hasanov', '01:00:00'),
    (489, 'Johan Jrnland', 'Gemensam lektion', 'v 38', '', 'Anton', 'Nessvi', '01:00:00'),
    (516, 'Johan Jrnland', 'Musikaliskt hantverk', 'v 38', 'ja', 'Emelie', 'Sjstrm', '01:40:00'),
    (519, 'Johan Jrnland', 'Musikaliskt hantverk', 'v 38', 'ja', 'Tomas', 'Wester', '01:40:00'),
    (523, 'Elin Bergstrand', 'Afroensemble', 'v 37', 'ja', 'Robin', 'Petersson', '01:00:00'),
    (524, 'Elin Bergstrand', 'Afroensemble', 'v 37', 'ja', 'Emelie', 'Sjstrm', '01:00:00'),

    /Jonas

  16. jonas,
    The query works correctly.
    pivot tables

    Maybe you having the same problem as tkbell..
    Did you execute the query in phpMyAdmin? If so, try execute the query without last statement.

  17. I'm having a diferente problem I count get any mention about this… I have in a exported table 93 columns, but only train, equipament are important to me; I need to have a count() from grouped information from train and equipment, but equipment must be a column, because I dont have records from all equipements for all trains to show in a stacked bars graph. So, my result shoud be like above:

    train, eq1, eq2,eq3,…
    train1 1, 0, 0, ,…
    train2 0, 10, 0, ,…

    Any ideas to have this result???

  18. First of all,

    thank you for writing such a brilliant article.
    I only have one question to ask:

    Is there any way that i can send the output of this command to a table so as to extract the data to a csv file?

    Thsnk you very much!

  19. Tepes4,

    Quote: Tepes4
    Is there any way that i can send the output of this command to a table so as to extract the data to a csv file?

    Just add the "CREATE TABLE New_Table_name AS" at the beginning of @sql

  20. This post is helped me alot but I have slightly complex Requirements

    this is table I need the result
    convert value column into row based on PageNo ,and RowNo

  21. Patrick,
    Is it possible to add a where clauses to it? For example if I only want the sizes small from the properties?
    Yes, it is possible. Use a subquery –

    Basavaraj,
    Please add more details to your question.

  22. Hello, I found your article very useful for my problem. More I have a doubt: I currently do a left join to cross information between my customer table and my table these phones customers a customer can have more than one phone. unfortunately the person who was working on this project before I had the bright idea of accumulating data and add 1 per phone line. why a client may have in the phones table 4 rows for his phones. I need to extract this data from the database when I put it increases the number of lines. I wonder if it is possible I dynamically create a column for each phone? example: if a customer has 20 lines with phones, turn into 20 columns?

    I thank anyone who can help me.

  23. jorgeslima,

    In this example, show in what format you need to get data from the database?
    What should be the result table?

  24. admin,

    A quick example of what i need is:

    Table 1 (customers):

    Table 2 (phones):

    Desired reult:

    not has to be exactly what is in the table result above but, something like the table result above will be very very useful.

    I will be thankful for any kind of help like documentation where i can find some explanation about what i need, or maybe few examples of similar scenarios where the problem was solved

    anyway, thanks for the help for now…

  25. @jorgeslima: Cannot say it is good way to apply custom pivoting for this purpose, the query may have very poor performance. I'd suggest you to use GROUP_CONCAT function to concat phone numbers in a one comma separated string, and do not use pivoting.

  26. In the above query i got a doubt if i need to get result for only two colors blue and orange then how should i pass the where condition for colours in the above query.please respond fast once u see my comment

  27. vayu,
    You must add HAVING clause:

  28. Hi,
    Just wanted to say thanks for this article. It took a while for me to get the dynapivot working, luckily your advice to Jonas helped (backticks for the win)

    I also wanted to point out to others who may have problems: be careful of invalid characters in possible future column names. After a few UPDATEs to remove the odd ',! and ?, the queries worked like a charm

    Cheers,

    Lee

  29. Hello, I tried to adapt the code with this query without any success. Can you help?

  30. I have query below:
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(S.don_vi_nhap = ''',S.don_vi_nhap,''',CONCAT(so_luong_ke_hoach ,":",so_luong_thuc_te), "0:0")) AS ',CONCAT("DV",countVar)
    )
    ) INTO @sql
    I want change name with standard above with countVar increase 1 after one column new. Can you help me? please.
    P/s: sorry, my english not good

  31. Thanks for these nice way for pivoting. Much better than multiple IF suggested by most others.
    The query works perfectly fine as normal query or as stored procedure in MySQL.

    However, I am running into an issue as I was thinking of creating a view so that it is ready and can be queried by developers as a table with where clauses as required.

    But view does't allow to call stored procedure or have variables sad
    What can be my alternatives?

Leave a Reply

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