[php]SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
‘MAX(IF(pa.meta_name = ”’,
meta_name,
”’, "pa.meta_value", NULL)) AS ‘,
meta_name
)
) INTO @sql
FROM post_meta;
SET @sql = CONCAT(‘SELECT p.id
, p.name
, p.type, ‘, @sql, ‘
FROM post p
LEFT JOIN post_meta AS pa
ON p.id = pa.post_id
GROUP BY p.id’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
[/php]
also this is the db structure may help any
[php]
DROP TABLE IF EXISTS `post`;
CREATE TABLE IF NOT EXISTS `post` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`type` varchar(50) DEFAULT NULL,
`order` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `post` (`id`, `name`, `type`, `order`) VALUES
(1, ‘product1’, ‘first product’, 5),
(2, ‘product2’, ‘second product’, 5);
DROP TABLE IF EXISTS `post_meta`;
CREATE TABLE IF NOT EXISTS `post_meta` (
`post_id` int(11) DEFAULT NULL,
`meta_name` varchar(10) DEFAULT NULL,
`meta_value` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `post_meta` (`post_id`, `meta_name`, `meta_value`) VALUES
(1, ‘size’, ‘\’\’\”),
(1, ‘height’, ‘103’),
(2, ‘size’, ‘L’),
(2, ‘title’, ’13’),
(2, ‘color’, ‘????’);
[/php]