- Example Data Create Table TB (ID INT, Name Varchar (10), Parentid Int) Insert Tb Select 1, 'AAAA', 0UNION All SELECT 2, 'BBBB', 0UNION All SELECT 3, 'CCCC', 0UNION All SELECT 4, 'AAAA-1', 1UNION All SELECT 5, 'AAAA-2', 1UNION All SELECT 6, 'BBBB-1', 2UNION All Select 7, 'CCCC-1', 3UNION All SELECT 8, 'CCCC-2 ', 3UNION All SELECT 9,' AAAA-1-1 ', 4GO
- Create a function crete function f_id () returns @re table (id int, level int, sid var) asbegin declare @L int set @ l = 0 insert @re select id, @ L, Right (10000 ID, 4) from Tb WHERE PARENTID = 0 While @@ Rowcount> 0 Begin Set @ L = @ L 1 Insert @re select a.id, @ L, B.SID ',' Right (10000 A. ID, 4) from TB A, @ re b where a.parentId = B.ID and b.level=@l-1 End ReturnendGo
- Call function Realization SELECT A. * With indent Name = space (B.LEVEL * 4) a.NameFrom TB A, F_ID () bwhere a.id = B.Idorder by B.SIDGO
- Delete Test DROP TABLE TBDROP FUNCTION F_ID
