SQLize Online / PHPize Online  /  SQLtest Online

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; WITH tt AS ( SELECT 1 as Level, Col1 as Folder, null as Parent FROM t UNION ALL SELECT 2 as Level, Col2 as Folder, Col1 as Parent FROM t UNION ALL SELECT 3 as Level, Col3 as Folder, Col2 as Parent FROM t UNION ALL SELECT 4 as Level, Col4 as Folder, Col3 as Parent FROM t ORDER BY Level, Parent, Folder) SELECT DENSE_RANK() OVER (ORDER BY Level, Parent,Folder) AS id, Level, Folder, DENSE_RANK() OVER (ORDER BY Level, Parent)-1 AS Parent2 FROM tt ORDER BY Level, Parent, Folder;