SQL Recursion with CTE Part 1 | Quick Tips Ep59
Вставка
- Опубліковано 9 лип 2024
- In this video I show you how to execute a recursive CTE (Common Table Expression) against Employee table. I demonstrate recursion and walk you through the different parts of recursive join. The demonstration uses SQL Server and SQL Server Management Studio (SSMS).
SQL Recursion with CTE Part 1 | Quick Tips Episode #59
TABLE OF CONTENTS
0:00 - Intro
0:20 - Regular CTE Example
1:15 - Recursive CTE Example - SQL
4:06 - Recursive CTE Results Walkthrough - Excel
7:48 - Recursive CTE with Hierarchy - Introduction
11:11 - Recursive CTE with Hierarchy - SQL
18:04 - Recursive CTE with Hierarchy Results Walkthrough - Excel
23:38 - Adding Levels to the Hierarchy
25:07 - Changing Anchor Record
25:48 - Adding Comma List of All Manager to Employee
31:42 - Pivoting Hierarchy for Reporting
UA-cam NEWS UPDATES
/ sqltrainingonline
VISIT SQLTRAININGONLINE.COM FOR MORE VIDEO NEWS & TIPS
www.sqltrainingonline.com
SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!
ua-cam.com/users/subscription_c...
SUBSCRIBE TO OUR EMAIL LIST!
www.sqltrainingonline.com
LET'S CONNECT!
LinkedIn: / joeyblue
SQLTrainingOnline: www.sqltrainingonline.com - Наука та технологія
The only video on the internet that helped me understand this concept clearly. Thanks a lot!
Took the words right from my mouth XD
you won everyone's heart when you said lets do one more excercise.
Thank you so much! 20+ years developer here in full stack (VB, C#, .NET), first time I actually understood the concept of CTE's and you helped me solve a very tricky problem I had, locating a mapping heirarchy 👍
Wow, thank you! This is an excellent explanation. I really prefer to understand what’s happening rather than just copying syntax, so this is much appreciated!
Best video on Recursive CTEs! Thanks a ton Joey!
Thank you for this very useful video!
Today I had a task to work with hierarchy in mssql and found your video . Thanks! It helped a lot :)
You saved my day, thanks lot for taking time and explained in a very clear way 🙏👍
after watching so many videos on this topic, i finally understand it thanks to you!
"sorry if I took so much time?" not everyone gets it in 2min video. Why does everyone think it is easy(they keep skipping many of the steps you explained, in their videos. ALL OF THAT HAPPENS AND ONLY THEN WE GET OUTPUT)? That excel sheet, iteration rows.. awesome. Thanks. You remind me of an underrated teacher who taught me C language. What you did to recursion, he did to pointers. tbh, I wondered whether this video would work before watching, by looking at those views and length of this video(actually, I spent more time than this video's length in figuring this recursion on my own, then watched 2 or 3 videos which didn't help and then landed here)
The very last explanation needs a deep level, but the rest is perfect!
Absolutely stunning explanation. Thank you much!
Very thorough and detailed explanation, thanks.
Extremely clear and helpful. Thank you!
Now I got it , thanks a lot
Great video !
Thanks for making this.
@Joey Blue just want to say: I appreciate Your work ;)
thx
Pretty advanced stuff
LOVE IT
Thank you!
Thank you ❤❤
Your way of explaining is very very good! Too bad that are not all like you
THANK YOU
Hi Joey,
Thank you for making such easy to understand videos.
Could you please make one video on recursive CTE to handle the case when employeeID and ManagerID are same?
At 35:52 when I add the CAST function to the CTE Anchor, I get this error:
Types don't match between the anchor and the recursive part in column "catlvl2" of recursive query "cat".
edit: I figured it out. Just an FYI for anyone else: the type and character length need to match. EXACTLY. After some digging, I noticed that my engineers set the column length to MAX, so when following this example in the video, instead of putting "cast (columnname (50))" I had to put "cast (columnname (MAX))"
Can we start at a low spot whre something is NULL in the hierarchy and work up util we get a vaule up the hierarchy?
HI, Great tutorial. Where can I get the db you're working with so I can practice myself?
How do you go the other way? I want to know who is at the apex of ward's leadership structure.
Thanx, really helped a lot! I thought I was going stupid, before this video. Btw. perhaps you should have concatenated the names instead of id first, that would have been simpler to wrap ones head around. But it was really well explained.
Hey Joey Blue...can you share the script to the heirarchy database...so we as students can try the recursive CTE ourselves? (Or is it one of the Adventureworks databases?)
Thanks for explanation. I have one question. What if the count of level is dynamic? Hierarchy in companies I not constans. I think about last part of the video.
Can you do a video on DIFFERENCE please?
How do I get a copy of this code?
Awesome video - I used the basic structure from the emp table to recurse through a table of about 250k rows. Running the recursion crashed my computer. I think it got overwhelmed having to join the 250k row table over an over again. Do you have any suggestions?
sucess