How to Make a Recursive SQL Query

Is there a way to make recursive SQL queries without using a CTE?

You don't nest CTEs, but they can be part of the insert:

with cte as (
      . . . 
     )
insert into . . .
   select . . .
   from cte;

You can see this in the syntax diagram for insert.

Note that in most databases, CTEs are part of the select, so you can do what you want.

Is it possible to make a recursive SQL query?

There are a few ways to do what you need in PostgreSQL.

• If you can install modules, look at the tablefunc contrib. It has a connectby() function that handles traversing trees. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• Also check out the ltree contrib, which you could adapt your table to use: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• Or you can traverse the tree yourself with a PL/PGSQL function.

Something like this:

create or replace function example_subtree (integer)
returns setof example as
'declare results record;
         child record;
 begin
  select into results * from example where parent_id = $1;
  if found then
    return next results;
    for child in select id from example
                  where parent_id = $1
      loop
        for temp in select * from example_subtree(child.id)
        loop
          return next temp;
        end loop;
      end loop;
  end if;
  return null;
end;' language 'plpgsql';

select sum(value) as value_sum
  from example_subtree(1234);

Simple recursive SQL query

I think you can find your answer here:
Postgresql recursive self join

Then this website is quite good to explain stuff like that:
https://www.postgresqltutorial.com/postgresql-recursive-query/

Here is an example with an employee / subordinate model:

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        manager_id,
        full_name
    FROM
        employees
    WHERE
        employee_id = 2
    UNION
        SELECT
            e.employee_id,
            e.manager_id,
            e.full_name
        FROM
            employees e
        INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
    *
FROM
    subordinates;

Create a table from the results of a recursive query (with statement)

Yes, it is possible(wrapping with additional SELECT * FROM ()):

CREATE TABLE factorials AS
SELECT *
FROM (
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i) s;

db<>fiddle demo

EDIT:

Actually, all you need to do is removing column list from CREATE TABLE:

CREATE TABLE factorials AS
WITH RECURSIVE fact_i (n, fct) AS (
     VALUES (0, 1)
     UNION ALL
     SELECT n+1, fct * (n+1) FROM fact_i
     WHERE n < 5)
SELECT * FROM fact_i;

db<>fiddle demo2

Recursive SQL query (parent-child) to include total downline records per record

OK, one possible way you could do it, not at all sure its the best way though.

1. Create a new column pathid based on the same principle as your existing path (name), but being unique per person.
2. Count how many times that id shows up in pathid other than ours by using a sub-query against the CTE.

WITH cte1 (PLID, sponsorid, firstname, lastname, [Status], [LEVEL], [path], pathid) AS (
    SELECT PLID, sponsorid, firstname, lastname, [Status], 0 AS [LEVEL]
      , CAST(firstname AS VARCHAR(1000)) AS [path]
      , CAST('/' + CAST(PLID AS varchar(38)) AS varchar(max)) AS pathid
    FROM #TEST
    WHERE PLID = 1--94
    UNION ALL
    SELECT c.PLID, c.sponsorid, c.firstname, c.lastname, c.[Status], cte1.[LEVEL] + 1 AS [LEVEL]
      , CAST((cte1.[path] + '/' + c.firstname) AS VARCHAR(1000)) AS [path]
      , CAST(cte1.pathid + '/' + cast(c.PLID AS varchar(38)) AS varchar(max)) AS pathid
    FROM #TEST c
    INNER JOIN cte1 ON c.sponsorid = cte1.plid
)
SELECT PLID, sponsorid, firstname, lastname, [Status], [LEVEL], [path], pathid
    , (select count(*) from cte1 B where B.pathid + '/' like '%/' + cast(A.PLID AS varchar(38)) + '/%' and B.PLID <> A.PLID)
FROM cte1 A
ORDER BY [path] ASC;

Returns for your sample data:

WITH RECURSIVE cte AS
(
SELECT
    s.component_no,
    p.description,
    s.quantity
FROM
    part p
    JOIN structure s ON 
        p.part_no = s.component_no
WHERE 
    s.part_no = 1 --the part number of what you are making goes here.

UNION ALL

SELECT
    s.component_no,
    p.description,
    s.quantity
FROM
    part p
    JOIN structure s ON 
       p.part_no = s.component_no
    JOIN cte ON 
       cte.component_no = s.part_no
)

SELECT 
   cte.description,
   SUM(cte.quantity) as quantity
FROM
   cte
GROUP BY
   cte.description

select *,300 +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by id rows unbounded preceding) Balance 
from Balances

select b.*, a.dateInsertion,a.amount, a.amount +  sum(isnull(addAmount,0) - ISNULL(abstractAmount,0))  over (order by b.id rows unbounded preceding) Balance 
from Balances b
cross join Amounts a
where a.dateInsertion = '20160707'