mysql - 使用 "|"作为分隔符将列转换为行

Question

请看这个 Fiddle Example

我想将每个字段转换为该表中的一行:

CREATE TABLE product
    (`ID` int, `name` varchar(1), `category` varchar(11), `price` int,`year`int)
;

INSERT INTO product
    (`ID`, `name`, `category`, `price`,`year`)
VALUES
    (1, 'A', 'Outdoor', 25,2010),
    (2, 'A', 'Doll', 34,2009),
    (3, 'C', 'Doll', 25,2008),
    (4, 'D', 'Outdoor', 20,2010),
    (5, 'E', 'Brainteaser', 22,2010),
    (6, 'E', 'Brainteaser', 22,2009),
    (7, 'G', 'Brainteaser', 30,2009),
    (8, 'G', 'Brainteaser', 30,2009)
;

这是我试图获得的输出:

field           value
name            A,C,D,E,G
category        Brainteaser,Doll,Outdoor
price           20,22,25,30,34
year            2008,2009,2010

我阅读了一篇关于使用 UNION 和 MAX 旋转表的帖子，但我迷失了使用 MAX 和 GROUP_CONCAT

   SELECT 
    MAX(CASE WHEN ... GROUP_CONCAT(DISTINCT (value) SEPARATOR '|')) as value
    from(
    select id,name value, 'name' field
    from product
    union all
    select id,category value, 'category' field 
    from product
    union all
    select id,price value, 'price' field
    from product
    union all
    select id,year value, 'year' field
    from product
      )
    GROUP BY field
    order by value

谁能告诉我如何获得该输出？

Answer 1

这会给你预期的输出:

SELECT 'name' AS `field`. GROUP_CONCAT(DISTINCT `name`  ORDER BY `name`) AS `value`
FROM product
UNION ALL
SELECT 'category' AS `field`. GROUP_CONCAT(DISTINCT `category` ORDER BY `category`) AS `value`
FROM product
UNION ALL
SELECT 'price' AS `field`. GROUP_CONCAT(DISTINCT `price` ORDER BY `price`) AS `value`
FROM product
UNION ALL
SELECT 'year' AS `field`. GROUP_CONCAT(DISTINCT `year` ORDER BY `year`) AS `value`
FROM product

添加了 ORDER BY 因为看起来你需要排序的输出