Recursive Query

Question

Recursive Query

Navigation

#1 by (2 votes)

4

I need to set up a recursive query to solve a simple problem, but I'm having a bit of trouble.

I have a table named TABELA1 with the following fields (ID, IDPAI, NAME)

I'll put some examples of records here and what I need.

Records in the original table:

HowImanagedtogetthemthroughmyquery:

USETESTEGOWITHNiveisAS(--MembroâncoraSELECTId,IdPai,Nome,0ASNivel--nível0FROMTABELA1WHEREIdPaiISNULLUNIONALL--FilhosSELECTT1.Id,T1.IdPai,T1.Nome,Nivel+1FROMTABELA1T1INNERJOINNiveisONT1.IdPai=Niveis.Id)SELECTId,IdPai,REPLICATE('',Nivel)+NomeASNomeFROMNiveisORDERBYId,IdPai

How do I need:

Resultofthegypsy'squery,comingclosetothesolution!

sql sql-server

asked by anonymous 24.02.2015 / 14:31

1 answer

Handling data output from the database in PHP array of simple arrays for json_encode

score 2 · Accepted Answer

Well, it just seems like an adjustment issue. Just concatenate the name of the previous level with the current level of recursion:

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 placed the conversion with 1000 characters to prevent the column from being too small and the levels not appearing.