Run Code  | API  | Code Wall  | Misc  | Feedback  | Login  | Theme  | Privacy  | Patreon 

list with indented categories

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl ( acctCode varchar(8) PRIMARY KEY, refAcctCode varchar(8) null, acctName varchar(32) );
INSERT INTO tbl VALUES 
 ('AC001',null,'SALES'),
 ('AC002',null,'AR'),
 ('AC003','AC001','FURNITURE'),
 ('AC004','AC003','OFFICE FURNITURE'),
 ('AC005','AC002','INVOICE'),
 ('AC006','AC001','UTILITIES');

DROP PROCEDURE IF EXISTS list;
CREATE PROCEDURE list()
BEGIN
 DROP TABLE IF EXISTS tmp; CREATE TABLE tmp (id varchar(1024), name varchar(128));
 SET @i=-5; SET @s=0;
 INSERT INTO tmp
 SELECT a.acctCode, a.acctName FROM tbl a WHERE a.refAcctCode is null;

 WHILE ROW_COUNT() > 0 DO
  SET @i=@i+6; SET @s=@s+2;
  INSERT INTO tmp
  SELECT CONCAT(id,' ',a.acctCode), CONCAT(SPACE(@s),a.acctName) 
  FROM tbl a INNER JOIN tmp b ON SUBSTR(id,@i)=a.refAcctCode;
 END WHILE;
 SELECT * FROM tmp ORDER BY id;
END;

CALL list();

   
    
 run  | edit  | history  | help 0