4
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!
You can put in your question how is your query until then?
– Leonel Sanches da Silva
Maybe I’d have to use
CASE
and sub-query. But I don’t think this would be cool for response time. Maybe in your code (should be c# or Vb) you can use aarray
to group the data (I have no way to provide an example because I don’t know what language you are using)– Guilherme Nascimento
I will use code C#. Thanks for the help, I’ve put the code in the question. ;-)
– Wallace Carlos
So @Wallacecarlos thought of using an array schema to group this data, because as I said, I believe that using sub-querys for each "loop" within the query can cause a small delay in data delivery (I hope I’m not talking nonsense).
– Guilherme Nascimento