stephenca

DROP TABLE IF EXISTS `attributes`;
CREATE TABLE  `attributes` (
  `name` VARCHAR(64) NOT NULL, 
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `attr_value_pairs`;
CREATE TABLE `attr_value_pairs` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `attr` VARCHAR(64) NOT NULL,

stephenca

!/usr/bin/perl 
use common::sense;

use DBI;

my $sql  = <<'INSERT';
INSERT INTO attr_value_pairs ( attr, value )
VALUES ( ?, ? )
INSERT

stephenca

SELECT  
  t.email,  
  GROUP_CONCAT(if(a.attr = 'title', value, NULL)) AS 'Title',
  GROUP_CONCAT(if(a.attr = 'forename', value, NULL)) AS 'First Name', 
  GROUP_CONCAT(if(a.attr = 'surname', value, NULL)) AS 'Last Name'
FROM targets t
JOIN attr_value_pairs a
ON t.attr_value_id = a.id
GROUP BY t.email;