This was certainly helpful. Thank you Steve. For those who were wondering how to make the hierarchy of a tree view sort correctly, you need to add a TreePath. After some experimentation I figured out how to do this. Here's the code in case it may be helpful to others: /* ------------------------------------------------------------------------------------------- CTE RECURSIVE QUERIES --------------------- ANCHOR QUERY START THE RECURSION ONE OR MORE ANCHOR QUERIES RECURSIVE QUERY THE PART THAT REPEATS ONE OR MORE RECURSIVE QUERIES MAXRECURSION THE NUMBER OF TIMES TO REPEAT THE RECURSIVE QUERY DEFAULT IS 100 MAXRECURSION OF 0 = INFINITE OPTION (MAXRECURSION 200); ------------------------------------------------------------------------------------------- NOTE: THE TREEPATH IS USED TO ORDER THE RESULTS SO THAT THE HIERARCHY BUILDS CORRECTLY ------------------------------------------------------------------------------------------- CREATE TABLE [dbo].[REF_Deptartment]( [DeptID] [int] IDENTITY(1,1) NOT NULL, [Department] [varchar](50) NULL, [ParentID] [int] NULL, CONSTRAINT [PK_REF_Deptartment] PRIMARY KEY CLUSTERED ( [DeptID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ------------------------------------------------------------------------------------------- DeptID Department ParentID 1 HQ 0 2 Finance 1 3 Accounting 2 4 InformationSystems 1 5 Programming 4 6 QA 5 7 HelpDesk 4 8 HandyTasks 7 9 AssistAccounting 3 ------------------------------------------------------------------------------------------- USAGE: EXEC S_CTE_Dept_Recursion ------------------------------------------------------------------------------------------- */ ALTER PROCEDURE S_CTE_Dept_Recursion as ;WITH DeptCTE(DeptID, Department, ParentID, lvl, TreePath) AS ( -- ANCHOR SECTION SELECT DeptID, Department, ParentID, 0 as Lvl, TreePath = CAST('ROOT' as VARCHAR(100)) FROM REF_Deptartment WHERE ParentID = 0 -- NOTE: THIS IS WHERE THE STARTING POINT FOR THE RECURSION IS AND CAN BE DONE BY PARENTID OR BY DEPTID -- WHERE DeptID = 4 UNION ALL -- THIS IS REQUIRED AS IT ADDS TO THE RESULTS FROM ABOVE --RECURSIVE SECTION - TAKE WHATEVER IS RETURNED FROM ABOVE AND USE THIS TO RECURSIVELY CONTINUE SELECT D1.DeptID, D1.Department, D1.ParentID, CTE.Lvl + 1 as Lvl, CAST(CTE.TreePath + '/' + RIGHT('000000000' + CAST(D1.DeptID AS VARCHAR(10)),10) AS VARCHAR(100)) FROM REF_Deptartment D1 INNER JOIN DeptCTE AS CTE ON D1.ParentID = CTE.DeptID ) SELECT REPLICATE('- ', lvl) + Department, TreePath FROM DeptCTE ORDER BY TreePath
hello Steve, as I request you please make a video that why's we use cross apply and outer apply within subquery/co-related subquery and what's benefits uses them.
Hello! I've been watching several of your videos (very good ones by the way) looking for a way to write a query with a recursive CTE to roll up the costs of a BOM from the bottom up. Can you tell me somewhere to get information or are you interested in making a video about it? Thank you!
It was available when the video was recorded, but unfortunately it is out of print now. Most of the content is available on my blog at stevestedman.com/category/classes/cte/
how do i convert this statement to CTE? UPDATE taggables, threads SET taggables.created_at = threads.created_at, taggables.updated_at = threads.updated_at WHERE taggables.thread_id = threads.id
@Q Its called critism..kikikikiki anyway on a serious note, the content was very good. Best thing Brad Stone could have done is sit down behind a laptop and go through the material slowly until IT SINKS IN
Thank you mate.
Very productive 👌
This was certainly helpful. Thank you Steve.
For those who were wondering how to make the hierarchy of a tree view sort correctly, you need to add a TreePath. After some experimentation I figured out how to do this. Here's the code in case it may be helpful to others:
/*
-------------------------------------------------------------------------------------------
CTE RECURSIVE QUERIES
---------------------
ANCHOR QUERY
START THE RECURSION
ONE OR MORE ANCHOR QUERIES
RECURSIVE QUERY
THE PART THAT REPEATS
ONE OR MORE RECURSIVE QUERIES
MAXRECURSION
THE NUMBER OF TIMES TO REPEAT THE RECURSIVE QUERY
DEFAULT IS 100
MAXRECURSION OF 0 = INFINITE
OPTION (MAXRECURSION 200);
-------------------------------------------------------------------------------------------
NOTE: THE TREEPATH IS USED TO ORDER THE RESULTS SO THAT THE HIERARCHY BUILDS CORRECTLY
-------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[REF_Deptartment](
[DeptID] [int] IDENTITY(1,1) NOT NULL,
[Department] [varchar](50) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_REF_Deptartment] PRIMARY KEY CLUSTERED
(
[DeptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-------------------------------------------------------------------------------------------
DeptID Department ParentID
1 HQ 0
2 Finance 1
3 Accounting 2
4 InformationSystems 1
5 Programming 4
6 QA 5
7 HelpDesk 4
8 HandyTasks 7
9 AssistAccounting 3
-------------------------------------------------------------------------------------------
USAGE:
EXEC S_CTE_Dept_Recursion
-------------------------------------------------------------------------------------------
*/
ALTER PROCEDURE S_CTE_Dept_Recursion
as
;WITH DeptCTE(DeptID, Department, ParentID, lvl, TreePath)
AS
(
-- ANCHOR SECTION
SELECT DeptID,
Department,
ParentID,
0 as Lvl,
TreePath = CAST('ROOT' as VARCHAR(100))
FROM REF_Deptartment
WHERE ParentID = 0 -- NOTE: THIS IS WHERE THE STARTING POINT FOR THE RECURSION IS AND CAN BE DONE BY PARENTID OR BY DEPTID
-- WHERE DeptID = 4
UNION ALL -- THIS IS REQUIRED AS IT ADDS TO THE RESULTS FROM ABOVE
--RECURSIVE SECTION - TAKE WHATEVER IS RETURNED FROM ABOVE AND USE THIS TO RECURSIVELY CONTINUE
SELECT
D1.DeptID,
D1.Department,
D1.ParentID,
CTE.Lvl + 1 as Lvl,
CAST(CTE.TreePath + '/' + RIGHT('000000000' + CAST(D1.DeptID AS VARCHAR(10)),10) AS VARCHAR(100))
FROM REF_Deptartment D1
INNER JOIN DeptCTE AS CTE
ON D1.ParentID = CTE.DeptID
)
SELECT REPLICATE('- ', lvl) + Department, TreePath FROM DeptCTE ORDER BY TreePath
Recursive CTE coverage starts at 28:58
Steve! You are the MAN!!!
excellent introduction to CTE, and its uses. I watched a few video's this was the most comprehensive. Thanks
Great video, excellent explanation of CTE's and their application! Thank you.
hello Steve, as I request you please make a video that why's we use cross apply and outer apply within subquery/co-related subquery and what's benefits uses them.
Thanks. This is deep about CTE. Help me a lot. Thank you from Brazil.
Glad it was helpful!
thank you to explain your knowledge, great content
Thanks a lot for your help. Where could I take sql data query from example to train?
Hello! I've been watching several of your videos (very good ones by the way) looking for a way to write a query with a recursive CTE to roll up the costs of a BOM from the bottom up. Can you tell me somewhere to get information or are you interested in making a video about it? Thank you!
Very good. I liked of the CTE example that used @result. I wanna do a cash flow with CTE! Thanks!!
Is your book on CTE available to purchase?
It was available when the video was recorded, but unfortunately it is out of print now. Most of the content is available on my blog at stevestedman.com/category/classes/cte/
Hi, and thanks for the videos. Are the scripts for creating the database and tables available online?
Thanks.
ruclips.net/video/0nGiCZ_3CIE/видео.html
how do i convert this statement to CTE?
UPDATE taggables, threads SET taggables.created_at = threads.created_at, taggables.updated_at = threads.updated_at WHERE taggables.thread_id = threads.id
If i were sittingin this lecture and paid money imwould demand it back. Rushed with indifference toward a student’s learning experience
@Q Its called critism..kikikikiki anyway on a serious note, the content was very good. Best thing Brad Stone could have done is sit down behind a laptop and go through the material slowly until IT SINKS IN