MySQL - Rows to Columns

Question

I tried to search posts  but I only found solutions for SQL Server Access  I need a solution in MySQL  5 X    I have a table  called history  with 3 columns  hostid  itemname  itemvalue  If I do a select  select   from history   it will return      -------- ---------- -----------       hostid   itemname   itemvalue       -------- ---------- -----------         1         A          10           -------- ---------- -----------         1         B           3           -------- ---------- -----------         2         A           9           -------- ---------- -----------         2         c          40           -------- ---------- -----------    How do I query the database to return something like      -------- ------ ----- -----       hostid     A     B     C        -------- ------ ----- -----         1       10     3     0        -------- ------ ----- -----         2        9     0     40       -------- ------ ----- -----

User · Answer

Another option especially useful if you have many items you need to pivot is to let mysql build the query for you   SELECT   GROUP CONCAT DISTINCT     CONCAT         ifnull SUM case when itemname              itemname            then itemvalue end  0  AS           itemname                INTO  sql FROM   history  SET  sql   CONCAT  SELECT hostid      sql                       FROM history                     GROUP BY hostid     PREPARE stmt FROM  sql  EXECUTE stmt  DEALLOCATE PREPARE stmt    FIDDLE Added some extra values to see it working  GROUP CONCAT has a default value of 1000 so if you have a really big query change this parameter before running it  SET SESSION group concat max len   1000000    Test    DROP TABLE IF EXISTS history  CREATE TABLE history  hostid INT  itemname VARCHAR 5   itemvalue INT    INSERT INTO history VALUES 1  A  10   1  B  3   2  A  9    2  C  40   2  D  5    3  A  14   3  B  67   3  D  8      hostid    A     B     C      D     1     10      3     0      0     2     9       0    40      5     3     14     67     0      8

User · Answer

I m going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem   I apologize if it s too long     I ll start out with the base you ve given and use it to define a couple of terms that I ll use for the rest of this post   This will be the base table   select   from history    -------- ---------- -----------    hostid   itemname   itemvalue    -------- ---------- -----------         1   A                 10          1   B                  3          2   A                  9          2   C                 40    -------- ---------- -----------    This will be our goal  the pretty pivot table   select   from history itemvalue pivot    -------- ------ ------ ------    hostid   A      B      C       -------- ------ ------ ------         1     10      3      0          2      9      0     40    -------- ------ ------ ------    Values in the history hostid column will become y-values in the pivot table   Values in the history itemname column will become x-values  for obvious reasons      When I have to solve the problem of creating a pivot table  I tackle it using a three-step process  with an optional fourth step     select the columns of interest  i e  y-values and x-values extend the base table with extra columns -- one for each x-value group and aggregate the extended table -- one group for each y-value  optional  prettify the aggregated table   Let s apply these steps to your problem and see what we get   Step 1  select columns of interest   In the desired result  hostid provides the y-values and itemname provides the x-values   Step 2  extend the base table with extra columns   We typically need one column per x-value   Recall that our x-value column is itemname    create view history extended as     select     history        case when itemname    A  then itemvalue end as A      case when itemname    B  then itemvalue end as B      case when itemname    C  then itemvalue end as C   from history     select   from history extended    -------- ---------- ----------- ------ ------ ------    hostid   itemname   itemvalue   A      B      C       -------- ---------- ----------- ------ ------ ------         1   A                 10     10   NULL   NULL          1   B                  3   NULL      3   NULL          2   A                  9      9   NULL   NULL          2   C                 40   NULL   NULL     40    -------- ---------- ----------- ------ ------ ------    Note that we didn t change the number of rows -- we just added extra columns   Also note the pattern of NULLs -- a row with itemname    A  has a non-null value for new column A  and null values for the other new columns   Step 3  group and aggregate the extended table   We need to group by hostid  since it provides the y-values   create view history itemvalue pivot as     select     hostid      sum A  as A      sum B  as B      sum C  as C   from history extended   group by hostid     select   from history itemvalue pivot    -------- ------ ------ ------    hostid   A      B      C       -------- ------ ------ ------         1     10      3   NULL          2      9   NULL     40    -------- ------ ------ ------     Note that we now have one row per y-value    Okay  we re almost there   We just need to get rid of those ugly NULLs   Step 4  prettify   We re just going to replace any null values with zeroes so the result set is nicer to look at   create view history itemvalue pivot pretty as     select      hostid       coalesce A  0  as A       coalesce B  0  as B       coalesce C  0  as C    from history itemvalue pivot      select   from history itemvalue pivot pretty    -------- ------ ------ ------    hostid   A      B      C       -------- ------ ------ ------         1     10      3      0          2      9      0     40    -------- ------ ------ ------    And we re done -- we ve built a nice  pretty pivot table using MySQL     Considerations when applying this procedure    what value to use in the extra columns   I used itemvalue in this example what  neutral  value to use in the extra columns   I used NULL  but it could also be 0 or     depending on your exact situation what aggregate function to use when grouping   I used sum  but count and max are also often used  max is often used when building one-row  objects  that had been spread across many rows  using multiple columns for y-values   This solution isn t limited to using a single column for the y-values -- just plug the extra columns into the group by clause  and don t forget to select them    Known limitations    this solution doesn t allow n columns in the pivot table -- each pivot column needs to be manually added when extending the base table   So for 5 or 10 x-values  this solution is nice   For 100  not so nice   There are some solutions with stored procedures generating a query  but they re ugly and difficult to get right   I currently don t know of a good way to solve this problem when the pivot table needs to have lots of columns

User · Answer

I figure out one way to make my reports converting rows to columns almost dynamic using simple querys  You can see and test it online here   The number of columns of query is fixed but the values are dynamic and based on values of rows  You can build it  So  I use one query to build the table header and another one to see the values   SELECT distinct concat   lt th gt   itemname   lt  th gt    as column name table header FROM history order by 1   SELECT      hostid       case when itemname    select distinct itemname from history a order by 1 limit 0 1  then itemvalue else    end  as col1       case when itemname    select distinct itemname from history a order by 1 limit 1 1  then itemvalue else    end  as col2       case when itemname    select distinct itemname from history a order by 1 limit 2 1  then itemvalue else    end  as col3       case when itemname    select distinct itemname from history a order by 1 limit 3 1  then itemvalue else    end  as col4 FROM history order by 1    You can summarize it  too   SELECT      hostid      sum case when itemname    select distinct itemname from history a order by 1 limit 0 1  then itemvalue end  as A      sum case when itemname    select distinct itemname from history a order by 1 limit 1 1  then itemvalue end  as B      sum case when itemname    select distinct itemname from history a order by 1 limit 2 1  then itemvalue end  as C FROM history group by hostid order by 1   -------- ------ ------ ------    hostid   A      B      C       -------- ------ ------ ------         1     10      3   NULL          2      9   NULL     40    -------- ------ ------ ------    Results of RexTester     http   rextester com ZSWKS28923  For one real example of use  this report bellow show in columns the hours of departures arrivals of boat bus with a visual schedule  You will see one additional column not used at the last col without confuse the visualization      ticketing system to of sell ticket online and presential

User · Answer

This isn t the exact answer you are looking for but it was a solution that i needed on my project and hope this helps someone   This will list 1 to n row items separated by commas  Group Concat makes this possible in MySQL     select cemetery cemetery id as  Cemetery ID   GROUP CONCAT distinct names name   as  Cemetery Name   cemetery latitude as Latitude  cemetery longitude as Longitude  c Contact Info  d Direction Type  d Directions      from cemetery     left join cemetery names on cemetery cemetery id   cemetery names cemetery id      left join names on cemetery names name id   names name id      left join cemetery contact on cemetery cemetery id   cemetery contact cemetery id       left join                select              cemetery contact cemetery id as cID              group concat contacts name  char 32   phone number  as Contact Info                  from cemetery contact                 left join contacts on cemetery contact contact id   contacts contact id                  left join phone on cemetery contact contact id   phone contact id               group by cID           as c on c cID   cemetery cemetery id       left join               select              cemetery id as dID               group concat direction type direction type  as Direction Type              group concat directions value   char 13   char 9   as Directions                  from directions                 left join direction type on directions type   direction type direction type id              group by dID             as d on d dID    cemetery cemetery id  group by Cemetery ID   This cemetery has two common names so the names are listed in different rows connected by a single id but two name ids and the query produces something like this    nbsp  nbsp  nbsp  nbsp CemeteryID  nbsp  nbsp  nbsp  nbsp Cemetery Name  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp Latitude  nbsp  nbsp  nbsp  nbsp 1  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  nbsp  Appleton Sulpher Springs  nbsp  nbsp 35 4276242832293

User · Answer

SELECT      hostid       sum  if  itemname    A   itemvalue  0     AS A        sum  if  itemname    B   itemvalue  0     AS B       sum  if  itemname    C   itemvalue  0     AS C  FROM      bob  GROUP BY      hostid

User · Answer

use subquery  SELECT  hostid        SELECT VALUE FROM TableTest WHERE ITEMNAME  A  AND hostid   t1 hostid  AS A       SELECT VALUE FROM TableTest WHERE ITEMNAME  B  AND hostid   t1 hostid  AS B       SELECT VALUE FROM TableTest WHERE ITEMNAME  C  AND hostid   t1 hostid  AS C FROM TableTest AS T1 GROUP BY hostid   but it will be a problem if sub query resulting more than a row  use further aggregate function in the subquery

User · Answer

Taking advantage of Matt Fenwick s idea that helped me to solve the problem  a lot of thanks   let s reduce it to only one query   select     history        coalesce sum case when itemname    A  then itemvalue end   0  as A      coalesce sum case when itemname    B  then itemvalue end   0  as B      coalesce sum case when itemname    C  then itemvalue end   0  as C from history group by hostid

User · Answer

I m sorry to say this and maybe I m not solving your problem exactly but PostgreSQL is 10 years older than MySQL and is extremely advanced compared to MySQL and there s many ways to achieve this easily  Install PostgreSQL and execute this query  CREATE EXTENSION tablefunc    then voila  And here s extensive documentation  PostgreSQL  Documentation  9 1  tablefunc or this query  CREATE EXTENSION hstore    then again voila  PostgreSQL  Documentation  9 0  hstore

User · Answer

My solution    select h hostid  sum ifnull h A 0   as A  sum ifnull h B 0   as B  sum ifnull h C 0   as  C from   select hostid  case when itemName    A  then itemvalue end as A  case when itemName    B  then itemvalue end as B  case when itemName    C  then itemvalue end as C   from history    h group by hostid   It produces the expected results in the submitted case

User · Answer

I make that into Group By hostId then it will show only first row with values  like   A   B  C 1  10 2      3

User · Answer

If you could use MariaDB there is a very very easy solution   Since MariaDB-10 02 there has been added a new storage engine called CONNECT that can help us to convert the results of another query or table into a pivot table  just like what you want  You can have a look at the docs   First of all install the connect storage engine   Now the pivot column of our table is itemname and the data for each item is located in itemvalue column  so we can have the result pivot table using this query   create table pivot table engine connect table type pivot tabname history option list  PivotCol itemname FncCol itemvalue     Now we can select what we want from the pivot table   select   from pivot table   More details here

User · Answer

I edit Agung Sagita s answer from subquery to join  I m not sure about how much difference between this 2 way  but just for another reference   SELECT  hostid  T2 VALUE AS A  T3 VALUE AS B  T4 VALUE AS C FROM TableTest AS T1 LEFT JOIN TableTest T2 ON T2 hostid T1 hostid AND T2 ITEMNAME  A  LEFT JOIN TableTest T3 ON T3 hostid T1 hostid AND T3 ITEMNAME  B  LEFT JOIN TableTest T4 ON T4 hostid T1 hostid AND T4 ITEMNAME  C

[mysql] MySQL - Rows to Columns

Examples related to mysql

Examples related to sql

Examples related to pivot-table

Examples related to etl

Examples related to crosstab