MySQL Dynamic Pivot From Nested Set

Question

I have the following schema and query which grabs each item in the nested set, and returns a comma separated list of its ancestors:

CREATE TABLE Tree
(title varchar(20) PRIMARY KEY,
 `tree` int,
 `left` int,
 `right` int);

INSERT Tree
VALUES
("Food", 1, 1, 24),
('Fruit', 1, 2, 13),
('Red', 1, 3, 8),
('Cherry', 1, 4, 7),
('Cherry_pie', 1, 5, 6),
('Yellow', 1, 9, 12),
('Banana', 1, 10, 11),
('Meat', 1, 14, 23),
('Beef', 1, 15, 16),
('Pork', 1, 17, 22),
('Bacon', 1, 18, 21),
('Bacon_Sandwich', 1, 19, 20);

Query

SELECT T0.title node
  ,(SELECT GROUP_CONCAT(T2.title ORDER BY T2.left)
                FROM Tree T2
                WHERE T2.left < T0.left AND T2.right > T0.right
                ) ancestors
FROM Tree T0
GROUP BY T0.title;

Fiddle: http://sqlfiddle.com/#!9/0a854/10

Result:

title          | ancestors
--------------------------
Bacon          | Food,Meat,Pork
Bacon_Sandwich | Food,Meat,Pork,Bacon
Banana         | Food,Fruit,Yellow
etc.....

I would like to pivot/separate the ancestors in to separate numerical headed columns, like so:

title          | 1    | 2      | 3      | 4    
----------------------------------------------
Bacon          | Food | Meat   | Pork   |
Bacon_Sandwich | Food | Meat   | Pork   | Bacon
Banana         | Food | Fruit  | Yellow |
etc.....

The ancestors could be literally anything, I have no way of knowing what or how many there will be.

I have no clue where to start with this, but if it helps I can use prepared statements, stored procedures, functions.. the whole shebang.

Answer 1

one way is to put your query into this. it will split the result from your query:

SELECT
  d.node
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 1)),',',1)) AS `1`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 2)),',',1)) AS `2`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 3)),',',1)) AS `3`
  , REVERSE(SUBSTRING_INDEX(REVERSE (SUBSTRING_INDEX(CONCAT(d.ancestors,',,,,'), ',', 4)),',',1)) AS `4`

FROM (
  SELECT T0.title node
        ,(SELECT GROUP_CONCAT(T2.title ORDER BY T2.left)
                      FROM Tree T2
                      WHERE T2.left < T0.left AND T2.right > T0.right
                      ) ancestors
  FROM Tree T0
  GROUP BY T0.title
) AS d;