動的行転列sql
3399 ワード
CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`count` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `count`) VALUES ('1', '6');
INSERT INTO `test` (`id`, `count`) VALUES ('2', '6');
INSERT INTO `test` (`id`, `count`) VALUES ('3', '8');
INSERT INTO `test` (`id`, `count`) VALUES ('4', '7');
#
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('max(IF(t.id = ', c.id,',t.count,0)) AS ''',c.id,'''')
) INTO @sql
FROM test c;
SELECT @sql;
SET @sql = CONCAT('Select ', @sql,
' from test t
');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;