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