Converting column into row using "|" as separators

Question

Please take a look at this Fiddle Example

I want to convert each field into a row from this table:

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)
;

Here's the output I'm trying to get:

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

I read a thread about pivoting table with UNION and MAX but I was lost at using MAX with 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

Can anyone show me how to get that output?

Answer 1

This will give you expected output:

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

Added ORDER BY because looks like you need sorted output