Reforming table structure of columns possible?

Question

I have bee at this all day. I have played around with a lot of MySQL code and I am either inept, or not very good at coding..

I have a table

name     id       type      amount
=================================
apple    21       cars      67
apple    21       bikes     85
apple    21       skates    557
apple    21       pajs      56
orange   34       bikes     345
orange   34       disks     678
orange   34       cars      234
orange   34       pajs      5678

I want to write a query that will bring back the table in this form

name    id cars bikes skates pajas disks
=========================================
apple   21 67   85    557    56    0    
orange  34 234  345   0      5678  678

I really just have no clue where to start. Sorry if this is noobie question but MySQL is really hard to conceptualize sometimes.

It's called a "pivot table". Suggest to do a Google search on that - there are plenty of questions here that also cover the topic. — lc., Oct 12 '12 at 06:34
Here is a [SO post on creating pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table). [There is a link given for a tutorial to create pivot tables. Have a look at that](http://www.artfulsoftware.com/infotree/queries.php#78) — jsist, Oct 12 '12 at 06:44

John Woo · Accepted Answer · 2012-10-12T06:44:23.010

5

Try using PreparedStatement

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(ID),MAX(case when type = ''',
      type,
      ''' then amount ELSE NULL end) AS ',
       type
    )
  ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT name, ', @sql, ' 
                   FROM table1 GROUP BY name');

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

SQLFiddle Demo

Alternatively,

SELECT  name,
        MAX(ID) ID,
        MAX(case when type = 'cars' THEN amount ELSE NULL end) AS cars,
        MAX(case when type = 'bikes' THEN amount ELSE NULL end) AS bikes,
        MAX(case when type = 'skates' THEN amount ELSE NULL end) AS skates,
        MAX(case when type = 'pajas' THEN amount ELSE NULL end) AS pajas,
        MAX(case when type = 'disks' THEN amount ELSE NULL end) AS disks
FROM table1 
GROUP BY name;

SQLFiddle Demo

edited Oct 12 '12 at 06:44

answered Oct 12 '12 at 06:38

John Woo

258,903
69
498
492

1

John Woo this is the second time you have helped me out with lightning speed code working perfectly. I don't even know what to say. Thank you so much. – Oct 12 '12 at 06:42
1

[Displaying zero instead of NULL in PreparedStatement](http://sqlfiddle.com/#!2/4f9a6/4) – John Woo Oct 12 '12 at 06:47
1

Absolutely flawless. Programmers like you is what I aspire to. – Oct 12 '12 at 06:49
Hey John, can I ask you a question? The query works great. But when I attempt here it does not work? The table structure is ok. Am I doing something wrong? sqlfiddle.com/#!2/264e8/1 – – Oct 12 '12 at 08:26
I think I know why. The procedure creates a table with a set of columns with names based on the the `status` field. If another `user_name` has a different number of rows with `status` then it will create more or less fields for that user and attempt to join it to the table. this one is going to be tough. – Oct 12 '12 at 08:33
@JohnWoo fyi, you don't need the `max(id)`, if you include it in the `GROUP BY` it will work without the additional aggregate. – Taryn Oct 12 '12 at 09:48

Reforming table structure of columns possible?

1 Answers1

SQLFiddle Demo

SQLFiddle Demo