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
2 answers
Many-to-many to recursive CTE in SQL Server
As I understand it, any many to many relationship is a hierarchy if you define one part as parent and another as child.
I have a situation where I need to get the children of an object that is a mapped to itself in a many to many table, but I am…
ColdAir
- 55
- 1
- 8
0
votes
1 answer
Types don't match between the anchor and the recursive part in column "ColumnName" of recursive query "CTE"
Trying to replicate Concatenation of multiple rows into one string-->https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/#Toc205129485
here is the query:
WITH CTE (id, CodeList, Code, Length)
AS…
Ingvar
- 11
- 5
0
votes
2 answers
Recursive CTE instead of scalar function
We have data warehouse and cube which is loaded from Microsoft Dynamics AX
Issue is caused by scalar function:
CREATE FUNCTION [dbo].[fn_SplitActivityByLevel]
(
-- Add the parameters for the function here
@Activity varchar(20),
…
Darko Milic
- 189
- 3
- 13
0
votes
1 answer
SQL Server 2014 Merging Overlapping Date Ranges
I have a table with 200.000 rows in a SQL Server 2014 database looking like this:
CREATE TABLE DateRanges
(
Contract VARCHAR(8),
Sector VARCHAR(8),
StartDate DATE,
EndDate DATE
);
INSERT INTO DateRanges (Contract, Sector,…
J.Farlov
- 13
- 2
0
votes
1 answer
How to sum a column in SQL Server recursive cte for optimization?
I have following table with hierarchical data:
FolderId ParentFolderId NumberOfAffectedItems
---------------------------------------------
1 NULL 2
2 1 3
3 2 5
4 2 3
5 …
Mohanraja
- 186
- 1
- 1
- 11
0
votes
1 answer
SQL Recursive CTE unexpectedly returns alternating sets
I am trying to get the use recursive CTE to repeat the same pattern over and over, resetting when "Scenario" increases in value. RowNumber repeats 1-21 (as desired), but whenever "Scenario" is an even number, there are too few items in the "Vals"…
happyhippo83
- 119
- 7
0
votes
1 answer
Calculating values in date ranges in Oracle (possibly with recursive CTE)
I have a problem which can be handled by a recursive CTE, but not within an acceptable period of time. Can anyone point me at ways to improve the performance and/or get the same result a different way?
Here's my scenario!
I have : A large table…
Caitlin M. Shaw
- 89
- 8
0
votes
2 answers
SQL server: How to build a date range from table with start and end times
I have a temp table containing 24 entries, one for each hour of a specific day annotated by DayID. The table contains an indication whether an application is on or off during that specific time range.
I would like to reduce the number of entries in…
Gert
- 39
- 4
0
votes
1 answer
Report Builder throws error over types mismatch in recursive CTE
I'm using a Recursive CTE I've found here to get dates within a range:
WITH T(d)
AS
(
SELECT @DateFrom
UNION ALL
SELECT CAST(DateAdd(DD,1,T.d) AS DATETIME) FROM T WHERE T.d < @DateTo
)
SELECT d FROM T OPTION (MAXRECURSION 32767);
This CTE runs…
OzW
- 848
- 1
- 11
- 24
0
votes
2 answers
The Recursive CTE to query all ancestors of a Parent-Child table is slow
We have a self-referenced table like this
CREATE TABLE Categories(
Id int IDENTITY(1,1) NOT NULL,
Title nvarchar(200) NOT NULL,
ParentId int NULL,
CONSTRAINT PK_Structures PRIMARY KEY CLUSTERED
(
Id ASC
)
CREATE NONCLUSTERED INDEX…
Alireza
- 5,421
- 5
- 34
- 67
0
votes
0 answers
SQL OpenQuery with Recursive CTE
I have the following Recursive CTE which is used in an OpenQuery function in SQL.
I use OpenQuery to query a SQL Linked Server.
(DOES NOT WORK)
SELECT * FROM OPENQUERY([MyLinkedServerDB], 'WITH SPChainCTE (NAME, INHERIT_FROM)
AS
(
Select…
user3605100
- 33
- 2
- 8
0
votes
2 answers
Is the outer WHERE clause optimized in a Recursive CTE?
With the following table definition:
CREATE TABLE Nodes(id INTEGER, child INTEGER);
INSERT INTO Nodes(id, child) VALUES(1, 10);
INSERT INTO Nodes(id, child) VALUES(1, 11);
INSERT INTO Nodes(id, child) VALUES(1, 12);
INSERT INTO Nodes(id, child)…
chacham15
- 13,719
- 26
- 104
- 207
0
votes
1 answer
How to translate "connect by" ORACLE to SQL Server
When using a RECURSIVE CTE in SQL Server, GROUP BY, LEFT OUTER JOIN and ORDER BY etc should not be used.
How can I translate this ORACLE query to SQL Server?
SELECT *
FROM (SELECT COALESCE(a.tree_id, ' ') MNUID,
…
user2427306
- 39
- 1
- 7
0
votes
1 answer
Call stored procedure into recursion cte
I have the following table
PNLTable([PnlId],
[Line],
[TotalisationId],
[Designation],
[Totalisation],
ParentId).
I use the following query to get for each pnlid all children
;WITH CTE
AS
(
…
user3635472
0
votes
2 answers
From a table of made up of rows of dates, how to count the number of rows where the date is in a certain date range
I have a table dbo.MyDates:
date
--------
'08/28/2012'
'01/10/2013'
'02/05/2013'
and a table dbo.People:
id name dateRangeStart dateRangeEnd
--- ------ -------------- ------------
100 Mike '08/01/2012' …
E.K. Fulton
- 5
- 1
- 1
- 5