Recursive query

Question

I need to mount a recursive query to solve a simple problem, but I’m having a little trouble.

I have a table called TABELA1 with the following fields (ID, IDPAI, NOME)

I will put here some examples of records and what I need.

Records in the original table:

How I managed to get them through my query:

USE TESTE
GO

WITH Niveis AS (
                -- Membro âncora
                SELECT Id, IdPai, Nome,
                0 AS Nivel -- nível 0
                FROM TABELA1
                WHERE IdPai IS NULL

                UNION ALL

                -- Filhos
                SELECT T1.Id, T1.IdPai, T1.Nome,
                Nivel+1
                FROM TABELA1 T1
                INNER JOIN Niveis ON T1.IdPai = Niveis.Id
                )
SELECT Id, IdPai, REPLICATE('     ' , Nivel) + Nome AS Nome
FROM Niveis
ORDER BY Id, IdPai

How I need to:

Result of the gypsy query, getting close to the solution!

Answer 1

Well, it seems to me only a matter of adjustment. Just concatenate the previous level name with the current recursion level:

USE TESTE
GO

WITH Niveis AS (
                -- Membro âncora
                SELECT Id, IdPai, convert(varchar(1000), Nome) as Nome,
                0 AS Nivel -- nível 0
                FROM TABELA1
                WHERE IdPai IS NULL

                UNION ALL

                -- Filhos
                SELECT T1.Id, T1.IdPai, convert(varchar(1000), Niveis.Nome + ' - ' + T1.Nome) as Nome,
                Nivel+1
                FROM TABELA1 T1
                INNER JOIN Niveis ON T1.IdPai = Niveis.Id
                )
SELECT Id, IdPai, Nome
FROM Niveis
ORDER BY Id, IdPai

---

EDIT

I forced the typing and column name to avoid the error reported by comment. I also put the conversion with 1000 characters to prevent the column gets too small and levels do not appear.