SQLize | PHPize | SQLtest

CREATE TABLE t ( Col1 VARCHAR(512), Col2 VARCHAR(512), Col3 VARCHAR(512), Col4 VARCHAR(512) ); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('C', '100', 'b', '1'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('C', '200', 'b', '2'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('B', '100', 'b', '3'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('B', '100', 'a', '4'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('B', '150', 'a', '5'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('B', '200', 'a', '6'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('A', '300', 'c', '7'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('A', '100', 'c', '8'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('A', '150', 'c', '9'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('A', '150', 'c', '10'); INSERT INTO t (Col1, Col2, Col3, Col4) VALUES ('A', '100', 'a', '11'); SELECT * FROM t ORDER BY Col1, Col2, Col3, Col4; set @counter_level3 = (SELECT DENSE_RANK() OVER (ORDER BY Col1) AS Parent_id FROM t ORDER BY Parent_id DESC LIMIT 1); set @counter_level4 = (SELECT DENSE_RANK() OVER (ORDER BY Col1, Col2) AS Parent_id FROM t ORDER BY Parent_id DESC LIMIT 1); set @counter_level5 = (SELECT DENSE_RANK() OVER (ORDER BY Col1, Col2, Col3) AS Parent_id FROM t ORDER BY Parent_id DESC LIMIT 1); WITH ct AS ( SELECT 1 as Level, DENSE_RANK() OVER (ORDER BY Col1) AS id, Col1 as Folder, null as Parent, null AS Parent_id FROM t UNION ALL SELECT 2 as Level, DENSE_RANK() OVER (ORDER BY Col1, Col2)+@counter_level3 AS id, Col2 as Folder, Col1 as Parent, DENSE_RANK() OVER (ORDER BY Col1) AS Parent_id FROM t UNION ALL SELECT 3 as Level, DENSE_RANK() OVER (ORDER BY Col1, Col2, Col3)+@counter_level3+@counter_level4 AS id, Col3 as Folder, Col2 as Parent, DENSE_RANK() OVER (ORDER BY Col1, Col2)+@counter_level3 AS Parent_id FROM t UNION ALL SELECT 4 as Level, DENSE_RANK() OVER (ORDER BY Col1, Col2, Col3, Col4)+@counter_level3+@counter_level4+@counter_level5 AS id, Col4 as Folder, Col3 as Parent, DENSE_RANK() OVER (ORDER BY Col1, Col2, Col3)+@counter_level3+@counter_level4 AS Parent_id FROM t ) SELECT row_number() over (partition by id order by id) as filter, Level, id, Folder, Parent, Parent_id FROM ct ;