Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » MySQL pivot tables (transform rows to columns)

MySQL pivot tables (transform rows to columns)

Author: rootsql Date: 31-10-2011, 23:14

Pivot tables are used to simplify extensive data in order to make it easier to read and understand. To pivot table, an aggregate is used against a set of data to distribute multiple rows of a single column into a single row with multiple columns.

CREATE TABLE properties (
  id INT(11) NOT NULL,
  item_id INT(11) DEFAULT NULL,
  prop VARCHAR(255) DEFAULT NULL,
  value VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO properties VALUES 
  (1, 1, 'color', 'blue'),
  (2, 1, 'size', 'large'),
  (3, 2, 'color', 'orange'),
  (4, 3, 'size', 'small'),
  (5, 4, 'color', 'violet'),
  (6, 5, 'color', 'green');
 

Pivot table output:

SELECT
  item_id,
  MAX(IF(prop = 'color', value, NULL)) AS color,
  MAX(IF(prop = 'size', value, NULL)) AS size
FROM
  properties
GROUP BY
  item_id;
+---------+--------+-------+
| item_id | color  | size  |
+---------+--------+-------+
|       1 | blue   | large |
|       2 | orange | NULL  |
|       3 | NULL   | small |
|       4 | violet | NULL  |
|       5 | green  | NULL  |
+---------+--------+-------+

Tags: Tips

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
hi here your work is pretty good. when i use this query i got an error as
1243 - Unknown prepared statement handler (stmt) given to EXECUTE
  • Group: Administrators
  • ICQ:
  • Registration: 27.09.2011
  • Comments: 5
  • Publications: 36
^
Hello Rosi, let me say this error cannot be raised here, because prepared statements are not used in this example. Just run all these statements as is, you should get correct dataset.
Information
Members of Guests cannot leave comments.