Welcome to Appsloveworld Technologies, in the Session, learn about recursive common table expressions, before continuing with this post, I strongly recommend you to read the below rticle of CTE.
I have this TblEmployeeManager table which has got EmpId, EmployeeName, and Manager_Id columns.
Since a manager is also an employee in that organization. We store both employee and manager details in the same table. Now if I ask you who is the manager of Michael, you will look up the Manager_Id column, which is number 5.
You will then take that value and look up in the empID column. Number 5 is Grayson. So Michael’s manager is Grayson.
we are taking the Manager_Id column and looking up another column in the same table. So you are referencing the same table. So it’s called a self-referencing table.
Now, if I ask you to display employee names along with their manager’s name, then we have to join this table with itself. And we know that joining a table with itself is called a self join.
we have talk about self join extensively in What is SELF JOIN ? with Realtime Example post.
So this is a simple self-join query obviously if I just want to display the employee names along with their manager names, we can make use of a simple self join.
Select Emp.EmployeeName as [Employee Name],
IsNull(Mng.EmployeeName, 'Super Boss') as [Manager Name]
from TblEmployeeManager Emp
left join TblEmployeeManager Mng
on Emp.Manager_Id = Mng.EmpId
if you look at Anthony Manager_Id is NULL, which means Anthony does not have any manager, so we are displaying “Super Manager” with the help of the IsNull function.
So if you just want to display employee name along with their manager’s name, you can simply use self-join.
Sql server hierarchical query example
But let’s say I want to display the employee name, their manager name, and along with that, I also want to display the level in the organization.
Now if you look at this organization chart below, Anthony is the super manager because he doesn’t have a manager_Id. Which means he’s at the top of the hierarchy. and then to Anthony, there are two direct reports.
Lincoln and Ryan, are the direct reports of Anthony.
if you look at Anthony, his employee Id=2, and Lincoln and Ryan, their manager_id is 2, So obviously, Lincoln and Ryan report to Anthony, and then Lincoln in a similar fashion has got one direct report.
Ryan also got one. So if you look at the hierarchy, Anthony, is that at the highest level and Jackson,Michael ,Luke and Jacob are at the bottom of the hierarchy.
So if you look at the levels of these employees, Anthony level is one, he’s at the top. And if you look at Lincoln and Ryan, they are level two.
you can see employees, Lincoln and Ryan, their level 2 along the same lines, Gabriel and Grayson has level three and at the bottom of the hierarchy, Jackson, Jacob, Michael, Luke with level four.
So right now, I want to display employee name, their manager name, and their level within the organization. Now, to achieve this, we can use recursive.
Using recursive CTE the code will become a lot easier.
With
EmployeesManagerCTE (EmpId, Name, ManagerId, [Level])
as
(
Select EmpId, EmployeeName, Manager_Id, 1
from TblEmployeeManager
where Manager_Id is null
union all
Select TblEmployeeManager.EmpId, TblEmployeeManager.EmployeeName,
TblEmployeeManager.Manager_Id, EmployeesManagerCTE.[Level] + 1
from TblEmployeeManager
join EmployeesManagerCTE
on TblEmployeeManager.Manager_Id = EmployeesManagerCTE.EmpId
)
Select EmpCTE.Name as Employee, Isnull(MgrCTE.Name, 'Super Boss') as Manager,
EmpCTE.[Level]
from EmployeesManagerCTE EmpCTE
left join EmployeesManagerCTE MgrCTE
on EmpCTE.ManagerId = MgrCTE.EmpId
we know that we create a CTE using the “WITH” keyword. So with EmployeesManagerCTE.
So here EmployeesManagerCTE is a CTE and then this is obviously having four columns, employeeId, name, Manager_Id, and level columns.
The reason why I have put level and square brackets are because the level is a keyword in SQL Server
if you want to use a keyword as a column name, then you will have to wrap that inside the square brackets.
if you look at the CTE query within the “AS” block, we are again, referencing the CTE within the CTE. So this kind of becomes a self-referencing CTE.
Table Script
/****** Object: Table [dbo].[TblEmployeeManager] Script Date: 09/22/2020 7:21:03 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblEmployeeManager](
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [nvarchar](max) NULL,
[Manager_Id] [int] NULL,
CONSTRAINT [PK_TblEmployeeManager] PRIMARY KEY CLUSTERED
(
[EmpId] ASC
)
)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] ON
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (1, 'Michael', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (2, 'Jackson', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (3, 'Jacob', 6)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (4, 'Luke', 5)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (5, 'Grayson', 8)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (6, 'Gabriel', 9)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (7, 'Anthony', NULL)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (8, 'Lincoln', 7)
GO
INSERT [dbo].[TblEmployeeManager] ([EmpId], [EmployeeName], [Manager_Id]) VALUES (9, 'Ryan', 7)
GO
SET IDENTITY_INSERT [dbo].[TblEmployeeManager] OFF
GO
The post How does Recursive CTE works in Sql server? appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- SQL Server 2005: dynamically adding parameters to a stored procedure
- Oracle and MS SQL Query very slow over VPN
- Classic ASP + TSQL: DateTime Format Issue
- Increment Row Number on Group for regular withdrawal amounts
- SQL SERVER: Check if variable is null and then assign statement for Where Clause
- Detemining a database's OS with a SQL query?
- MS SQL rollup percentage column
- [Azure/mssql]How do I get Insert query recordset
- How to extract SSIS package tasks information?
- ordering street address in MS SQL
- Geting Subquery result based on other subquery
- Dynamic SQL results into temp table in SQL Stored procedure
- SQL Double Group by query
- INSERT INTO if not exists SQL server
- HH:MM Format in SQL Server
- Selecting missing entries in SQL Server
- Transact-SQL local variable with case statement
- Reuse the same query but just update the value of the nested variable
- TSQL Translate ChangeData Field in CRM 2011
- Terminate IF block in Transact-SQL
- Connecting to MSSQL server using LAMP
- SQL query to count total amount of time
- How can I send a string as NULL to SQLServer using Dapper?
- Unable to build SQL Server Project V12 because of AT TIME ZONE
- How to find records that don't have a value in SQL Server 2014?
- only 1 value returns with sql query
- Adding a Total column to MDX
- Linked Server vs. Ad Hoc (OpenRowset / OpenDatasource) Distributed Queries
- .NET C# - Cannot access a SQL Server database behind remote machine
- Display specific columns in Sql Tables
- Alter scalar function in SQL server 2008 that referring in the computed column of a table
- Get Table Columns With Primary Key Constraints
- SQL Server 2005 - Check for Null DateTime Value
- TSQL check constraint related to existing rows
- sql delete query
- SQL Server: Pulling updated data from a function during a CROSS APPLY
- Preventing aggregation along dimensions' attributes
- SQL Query builder utility
- Pivoting record values to column names with joined tables
- GROUP BY issue when SELECT to dataGridView from 2 tables
- Trying to pull most recent pay rate from table
- SQL column does not fetching NULL
- Excluding records in select when rank functions is not enough
- SQL Server select rows from, to
- query plan shows cost of 54% for an insert when no rows actually involved
- SQL and escaped XML data
- Stored Procedure Transaction
- Generalize SQL query to include data for all Users
- how can i change a field in my SQL database from numeric(18) to varchar(10)
- Using "varchar" as the primary key? bad idea? or ok?
- SQL-Server Time data type
- Need To Pull Most Recent Record By Timestamp Per Unique ID
- Transaction, Table Truncate and Parametrized SQL Query
- Why are nested loops chosen causing long execution time for "self join"
- Date in SELECT statement is always the same
- I need to be able to generate non-repetitive 8 character random alphanumeric for 2.5 million records
- Display NULL if some value is not found in SQL
- Can entity framework database-first successfully build models from any SQLServer db?
- Pairwise swapping of rows in sql
- Find chain of columns values
- Deterministic scalar function to get week of year for a date
- SQL Adding rows to my dataset as placeholders for missing groups?
- How to import Microsoft.SqlServer.Types into Microsoft SQL Server 2012?
- Two joins between two tables
- Grouped random sample MS SQL Server
- Top 1 with a left join
- Two different update statements - Only one working
- PHP - mssql_connect(): Unable to connect to server
- how can i capture null values from mssql_result?
- Merge the rows and give me the total count
- Huge Errorlog with SQL Server 2005 Express (15GB)
- Not able to perform Query Grouping
- SSAS: programmatically & permanently create calculated member
- How can I enable Full-Text indexing using SQL Server Management Studio in SQL Server 2008 Express?
- Best way to add SSIS (Business Intelligence) projects to SourceSafe?
- Cross domain windows authentication SSMS Error SQL Server 2012 - pre-login handshake failed
- How to make stored procedure dynamic search with SQL Server
- SQL - Take data from multiple rows into single row
- Access linked tables truncating my Decimal values from the SQL server
- Round off Value in sql query
- How can I add numbers in Transact SQL?
- Move SQL Server data in limited (1000 row) chunks
- Stored procedure to re-order rows in a table
- DuplicateKeyException after deleting record in an other program
- How to prevent a self-referencing table from becoming circular
- Select TOP row in SQL and get 5 rows of end table
- Storing a 30KB BLOB in SQL Server 2005
- Summarize log data into change history with SQL statement
- System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'FROM'
- Using data from query
- Does using Aliases with AS slow down Query Speed?
- How to convert jdbc template query result to JSON Array in java?
- How to find highest values within groups?
- INSERT INTO sql server error : invalid object name
- Retrieving SQL Server Full Text Index terms
- SQL Where clause Coalese vs ISNULL VS Dynamic
- How to return one row with multiple column values?
- The following SQL is taking long time to execute. Is there anyway to speed it up?
- SQL Server - Combine two select queries
- How to delete old aspnet users with aspnet_Users_DeleteUser procedure?
- Linq-to-SQL: how many datacontexts?
- How to pivot without an aggregate?
- SQL Server: how do I show placeholder rows from a case statement?
- How to avoid nested keys and values while joining tables and using 'for json auto'
- how do i get hierarchy of data from two different tables?
- Update a Column that has been added in the same script
- How to generate 100000 no repeat random numbers in SQL SERVER2008
- Create table to store hours
- Getting correct data type of columns from view
- String or Binary Data Would be Truncated…
- How to transpose rows/columns without aggregation?
- Bulk Insert failing in SQL Server "Column is too long"
- Execute a SQL String and insert result into table
- C# SQLServer retrieving results and place in a .csv format
- SQL Server : how to select a fixed amount of rows (select every x-th value)
- EF Core - Error "The association between entities has been severed" while deleting child entity from parent
- Can't save picture in database
- How do you unflatten a delimited string column field to separate rows in SQL?
- How to use date and integer as invoice number in asp.net
- SQL Server ISNULL not working
- BiDirectional Sync Provisioning Between SQL CE and SQL Server
- What's the best way send email from the database in SQL Server 2008 R2?
- How to mitigate locking in stored procedure that queries cross-database view
- Force DateTime with Entity Framework Database First
- invoking trigger on column update
- Can i write this group by query without group by?
- What performance difference can I expect from enforcing PK/FK constraints vs not enforcing PK/FK constraints if any?
- Create a filtered index that will work with ANSI_NULLS OFF
- TSQL - Disabling Triggers in Transactions
- SET IDENTITY_INSERT TableName ON does not work on Entity Framework Core 2.2
- Referential integrity in SQL Server
- OLE DB Command DT_NTEXT Output Type and XML input
- Using RowNumber and Partition
- What's the effect of including an "include" column in a non-clustered index that's already part of the clustering key?
- Implementing debit-credit resolution system in SQL query
- Get top 1 row of each group
- SQL Server different design query performance
- SQL Server non-clustered Index never shows up in execution plan
- How to get Exact Time format using select query
- SQL - How do you use a user defined function to constrain a value between 2 tables
- Calculating number of clients accessing database at the same time
- SQL Server 2000 - What really is the "Actual Number of Rows"?
- What does "foo" mean in this SQL Server Query?
- Error converting data types when importing from Excel to SQL Server 2008
- EJB3 case-sensitive annotations
- CAST always returns 1
- SQL Server---how to group by a column that is declared in CASE statement in SELECT statement
- Slow performance when using Paged Fetch
- How can I return a row if its ID is not found in another table?
- The fastest way to copy a huge dataset to server in SAS