MySQL examples and queries:
http://www.artfulsoftware.com/infotree/queries.php
This great resource showed me a simple way to pivot in MySQL. If you have the following tables:
CREATE TABLE `project` ( `project_id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`project_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE `project_service` ( `project_id` int(11) NOT NULL, `service_id` int(11) NOT NULL, UNIQUE KEY `project_id` (`project_id`,`service_id`), KEY `service_id` (`service_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `service` ( `service_id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, `short_name` varchar(5) NOT NULL, PRIMARY KEY (`service_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
The result I wanted was to show a project name and its services all in one row. To accomplish this the website said to use GROUP_CONCAT like so:
select p.name, GROUP_CONCAT(if(s.service_id = 1, '1', NULL)) AS 'Service1', GROUP_CONCAT(if(s.service_id = 2, '1', NULL)) AS 'Service2', GROUP_CONCAT(if(s.service_id = 3, '1', NULL)) AS 'Service3' from project as p inner join project_service as ps on (p.project_id = ps.project_id) inner join service as s on (s.service_id = ps.service_id)
With that query I can see a 1 for each of the project’s services. Please see the pivot section for more details.