嵌套和不嵌套

Question

我有一个类似的表格

name                value
-----        |      ------
rami         |      2
rami         |      3
rami         |      4
sam                 5
sam                 6

我想用一种方法来获得这个结果

Rami      sam
-----     -----
2,3,4     5,6

Answer 1

这里需要一个稍微复杂一些的数据透视表

用户定义的变量@sql获取名称并添加值外部选择由@sql2 进行

CREATE TABLE name_val
(`name` varchar(4), `value` int)
;

INSERT INTO name_val
(`name`, `value`)
VALUES
('rami', 2),
('rami', 3),
('rami', 4),
('sam', 5),
('sam', 6)
;

SET @sql = NULL;
SET @sql2 = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('GROUP_CONCAT(IF(s.`name` = "', `name`,'", `value`,"")) AS ',name)
) INTO @sql
FROM name_val;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(', `name`,') AS ',name)
) INTO @sql2
FROM name_val;
SET @sql = CONCAT('SELECT ',@sql2,' FROM (SELECT ', @sql, ' 
FROM name_val s
GROUP BY s.`name`
ORDER BY s.`name`) t1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

rami|sam：----|：--2,3,4|5,6

db<gt；小提琴这里

Answer 2

只需使用条件聚合：

select group_concat(case when name = 'Rami' then value end order by value) as rami,
group_concat(case when name = 'Sam' then value end order by value) as sam
from t;