Select of Product Structure

Question

Good morning!

there is some way to create a query that brings the "product" and all the "Components and Subcomponents" of the structure of this product?

Let me try to explain better

Structure

Imagery

Image Link: https://i.stack.Imgur.com/tGNZ4.jpg

Text format

- Bicicleta
  |
  + - Guidão
  |
  + - Quadro
  |   |
  |   + - Garfo
  |
  + - Banco
  |   |
  |   + - Selim
  |
  + - Roda
      |
      + - Aro
          |
          + - Raio
          |
          + - Pneu
              |
              + - Camara

I have the "bike" equipment structure inserted in the database and I need to get who is the "product" and all that are below (Components[eCOMP] and Subcomponents[eCOMP]) connecting all to the main product

Example

+--------+----------+--------+----------+
|eCOD    |eDESC2    |eCOMP   | eDESC2   |
+--------+----------+--------+----------+
|01.0001 |Bicicleta |01.0002 | Guidão   |
|01.0001 |Bicicleta |01.0003 | Quadro   |
|01.0001 |Bicicleta |01.0004 | Garfo    |
|01.0001 |Bicicleta |01.0005 | Banco    |
|01.0001 |Bicicleta |01.0006 | Selim    |
|01.0001 |Bicicleta |01.0007 | Roda     |
|01.0001 |Bicicleta |01.0008 | Aro      |
|01.0001 |Bicicleta |01.0009 | Raio     |
|01.0001 |Bicicleta |01.0010 | Pneu     |
|01.0001 |Bicicleta |01.0011 | Camara   |
+--------+----------+--------+----------+

Table structure

Data in Sqlfiddle

Data in Text format

Cadastro de Produto [Tabela PROD]
pCOD     pDESC
01.0001  Bicicleta
01.0002  Guidão
01.0003  Quadro
01.0004  Garfo
01.0005  Banco
01.0006  Selim
01.0007  Roda
01.0008  Aro
01.0009  Raio
01.0010  Pneu
01.0011  Camara

Estrutura do Produto [Tabela ESTR]
eCOD     eCOMP    eQTD  eNiv
01.0001  01.0002     1     1
01.0001  01.0003     1     1
01.0003  01.0004     1     2
01.0001  01.0005     1     1
01.0005  01.0006     1     2
01.0001  01.0007     2     1
01.0007  01.0008     1     2
01.0008  01.0009     1     3
01.0008  01.0010     1     3
01.0010  01.0011     1     4

Data in SQL Format

CREATE TABLE PROD (
 pCOD varchar(15),
 pDESC varchar(100)
 );

insert into PROD (pCOD, pDESC) values
('01.0001','Bicicleta'),
('01.0002','Guidão'),
('01.0003','Quadro'),
('01.0004','Garfo'),
('01.0005','Banco'),
('01.0006','Selim'),
('01.0007','Roda'),
('01.0008','Aro'),
('01.0009','Raio'),
('01.0010','Pneu'),
('01.0011','Camara')

CREATE TABLE ESTR (
 eCOD varchar(15),
 eCOMP varchar(15),
 eQTD integer,
 eNIV integer
 );

insert into ESTR (eCOD, eCOMP, eQTD, eNIV) values
('01.0001','01.0002','1','1'),
('01.0001','01.0003','1','1'),
('01.0003','01.0004','1','2'),
('01.0001','01.0005','1','1'),
('01.0005','01.0006','1','2'),
('01.0001','01.0007','2','1'),
('01.0007','01.0008','1','2'),
('01.0008','01.0009','1','3'),
('01.0008','01.0010','1','3'),
('01.0010','01.0011','1','4')

Answer 1

It is possible to arrive at the result you want in two ways: creating a temporary table and filling, row by row, from a CURSOR (or loop), which is more laborious because it will have to make the query for each level and in terms of performance (by default) is slower, or simply uses CTE:

;WITH REC AS
(
    SELECT      E.*
    FROM        ESTR E
    INNER JOIN  PROD P ON P.pCOD = E.eCOD
    WHERE       E.eNIV = 1
    UNION ALL
    SELECT      R.eCOD, E.eCOMP, E.eQTD, E.eNIV
    FROM        ESTR E
    INNER JOIN  REC  R ON R.ecomp = E.ecod
)
SELECT      R.eCOD
        ,   P1.pDESC
        ,   R.eCOMP
        ,   P2.pDESC
        ,   R.eNIV
FROM        REC     R
INNER JOIN  PROD    P1 ON P1.pCOD = R.eCOD
INNER JOIN  PROD    P2 ON P2.pCOD = R.eCOMP
ORDER BY    R.eCOD
        ,   R.eNIV

You can test here on Sqlfiddle.