A Recursive Common Table Expression in SQL is a Common Table Expression (CTE, also known as "WITH clause") that refers to itself recursively. The temporary result set is usually computed by iteration. Use this tag for questions concerning the definition and use of recursive CTEs.
Questions tagged [recursive-cte]
133 questions
0
votes
1 answer
Find all composite type component types using WITH RECURSIVE
Given the typname of a composite type, how do I find the type oids of all component types recursively?
Example:
CREATE TYPE t_station AS (x INT,
y INT,
label VARCHAR);
CREATE TYPE t_address AS…
oberstet
- 21,353
- 10
- 64
- 97
0
votes
1 answer
Unable to find the matching child record using recursive CTE
I have the following table @t:
ParentId SkuName ChildId
P1 X1 C1
C1 X2 C2
C2 X3 C2
If I pass the ParentId = P1, the desired output is x3
i.e. the stopping condition is the last row matching record…
user1323981
-1
votes
1 answer
Finding salary of all the employee of all the supervisor where supervisor can have supervisor
Problem
I have two tables. employee and salary.
For the employee table I have employee_id and supervisor_id.
employee_id
supervisor_id
0
5
1
5
2
5
3
6
4
6
5
7
6
8
7
NULL
8
NULL
For the salary table I have amount and…
mu_sa
- 2,685
- 10
- 39
- 58
-1
votes
1 answer
How to select all participants who made at least 1 submission each day for everyday?
I have a problem like this in SQL Server. Here is the base…
Hoang Minh Quang FX15045
- 733
- 1
- 4
- 15
-1
votes
1 answer
How to use recursive ctes to see if there are any contiguous date range and combine those date ranges into a single one?
I am trying to look at my database of administered table where we can see when a member has been administered and released from the rehab center. The sample table would look something like this:
SAMPLE…
Lia Lia
- 17
- 1
- 3
-1
votes
1 answer
I need to convert this query from Oracle to SQL Server
SELECT
ffl2.fee_record_code,
(SELECT max(fee_record_code)
FROM fees_list ffl3
START WITH ffl3.fee_record_code = Nvl(ffl2.fes_associated_record, ffl2.fee_record_code)
CONNECT BY PRIOR ffl3.fee_record_code =…
simran kukreja
- 3
- 1
-1
votes
1 answer
Recursive CTE to find Total for all children parent and depth
I hope use Recursive CTE to find Total for all childrencount and parentcount and path and level and currentlevel like this
Id ParentId Name Path Level CurrentLevel ChildrenCount ParentCount
1 NULL a 1 4…
beiduoan
- 37
- 7
-1
votes
3 answers
How do I get a list of corporations where corporation (ID) is included?
IMPORTANT ... in my RL project, the ID's aren't INT, but GUID, so my data is NOT hierarchical!
I have a table with companies and a table with links between companies.
I need to be able to retrieve a list of companies from a specific company…
MojoDK
- 4,410
- 10
- 42
- 80
-1
votes
3 answers
Display all the values which start with first letter of each string separated by any delimiter
I have input column name
Ahar
Aris
Suchi
Doll
Dipti
I want the output to be with two columns
FirstLetter TotalsWordsWithThatLetter
A Ahar, Aris
S Suchi
D Doll,Dipti
I know recursive CTE/stuff function will be of help. But unable to write complete…
user3292140
- 1
- 2
-1
votes
1 answer
Types don't match between the anchor and the recursive
I have problem with sql query error with recursive dates
Types don't match between the anchor and the recursive part in
column "CalendarDate" of recursive query "Dates".'
";WITH Dates as ("
+ " SELECT @FromDate as CalendarDate "
+…
Aditya Thota
- 1
- 1
- 1
-1
votes
1 answer
count number of hierarchical childrens in sql
I have a table that stores parent and left child and right child information. How do i count number of children belongs that parent?
for example my table structure is:
parent left right
--------------------
1 2 3
3 4 5
4 …
Mj.
- 321
- 1
- 6
- 13
-2
votes
1 answer
List all the ancestors or parent nodes of a particular node present at each level in pivoted table containing levels as attributes in SQL Server
I have a table 'temp' which has id and its immediate parent's id as columns. The table is as follows:
1
/ \
2 3
/|\ \
…
davis_john
- 57
- 1
- 6
-4
votes
1 answer
need to know about the recursive view in snowflake
I'm new to snowflake. And I need to know about the recursive view in snowflake. Can someone explain it with an example. And why recursive views are used. Non-Materialized view only
I need a really good example. Thank u in advance
CREATE OR REPLACE…
dona
- 3
- 2