动态行转列sql

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;