SQL Server - Common Table Expressions

In introduction to using Common Table Expressions on SQL Server, starting with simple CTE's, and eventually diving into advanced CTE techniques for Data Recursion

[vbywrde]

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 + '/' + + 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

[kevinm]

Recursive CTE coverage starts at 28:58

[KiranSingh-bkuz]

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.

[TheAndozio]

Thanks. This is deep about CTE. Help me a lot. Thank you from Brazil.

[MrPoppies]

excellent introduction to CTE, and its uses. I watched a few video's this was the most comprehensive. Thanks

[redgesemplonius]

Great video, excellent explanation of CTE's and their application! Thank you.

[lazargumarov]

Thanks a lot for your help. Where could I take sql data query from example to train?

[osito]

thank you to explain your knowledge, great content

[guillermovenuto]

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!

[brand]

Is your book on CTE available to purchase?

[dennesmenezes]

Very good. I liked of the CTE example that used @result. I wanna do a cash flow with CTE! Thanks!!

[mutazsayegh]

Hi, and thanks for the videos. Are the scripts for creating the database and tables available online?

Thanks.

[richardselaseannum]

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

[bradstone]

If i were sittingin this lecture and paid money imwould demand it back. Rushed with indifference toward a student’s learning experience