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
- Painfully slow query, what are my options?
- mssql cdc update_mask filter changes made only in column TS
- Compare two rows (both with different ID) & check if their column values are exactly the same. All rows & columns are in the same table
- Query speed in SQL Server Management Studio (2008) vs NHibernate session.Query
- SQL replace incomplete days of the week in the month to 0
- returning a bit type in a stored procedure
- Pull data from Redshift
- Count Value For Conditionals
- sql server for xml clause
- How do I get around a 'circular reference' in an Inner join
- MS SQL 2012 : In SQL Shift columns to left side if column contains 0
- Is there a quick way to determine what tables are referencing a specific primary key as a foreign key?
- What happens if I dont open instance of SqlConnection?
- How - create and use database directly after creation in SQL Server?
- Rhino ETL - loading large pipe-delimited files
- How to find what products sell well with others in an SQL Server Query
- How to replace underscore to space by using t-sql?
- Truly empty element with sql server for xml-directive
- Display Data as Ledger
- SSIS: An OLE DB Error has occurred. error code 0x80004005, The peer prematurely closed the connection
- EOutOfMemory when open TFDQuery for one record
- UPDATE with IF/ELSE
- Building AST from T-SQL
- Insert record to a table, update next sequence number based on record just inserted, then insert next record
- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=,
- calling one stored proc from another by executing command
- SQL Server, Find duplicate rows base on a column
- Parentheses Placement in Where Statements
- Formatting multiple phone numbers with Parenthesis[ () ] and hyphen( - ) in SQL Server
- Updating TSQL Column From a JSON_VALUE in Another Table?
- How can I change password for sql login to database using pyodbc?
- XML from webservice to SQL Server using SSIS
- How to group datas by week in sql?
- will sql server 2008 lock source table while copying table from one database to another
- SQL Joins and Conditional Sums
- Most efficient way to return AND USE a single row query from SQL Server?
- insert a multiple rows and a common value using stored procedures?
- Getting current connection properties in SQL Server
- Transaction management and temporary tables in SQL Server
- In my sql script LEFT JOIN is giving output like CROSS JOIN?
- "SqlDateTime overflow" error with DataSets over remoting
- SQL Server Pivot with Date Ranges as Columns
- String concatenation with escaped variables
- SQL Server get all the records including null values if the parameter is null
- Why getting second highest list product from this query is not working
- Optimizing SQL query
- SQL Server 2005 : ALTER COLUMN AUTO_INCREMENT and set started ID of AUTO_INCREMENT
- Setup of DG4SQL Gateway from Oracle 11.2 to SQL 2005
- How to return the table name used out of a delete statement?
- Keeping the results with GROUP BY
- Update / Insert trigger in SQL Server
- Deleting Duplicate Records from a Table
- How to know when to Commit or Rollback a Transaction
- How to present hierarchical data in SQL Server 2014
- How to implement C# code for Order id separated by commas and range separated by hyphens, and display all info of order
- Execute stored procedure only once but pass different parameter values 10 times
- id where column has two conditions
- SQL Server Impersonation and Connection Pooling
- sql string or binary data truncated
- Merge search multiple condition - SQL Server
- SQL Case statement specifiying condition in where clause?
- Insert Large dictionary in MS-SQL Server using Stored Procedure
- Error sqlcommand.ExecuteNonQuery()
- How to create temporary non cluster index on my searching table Columns and remove it after select operation
- Creating custom assembly in SQL Server throwing assembly not found in database error
- Hibernate id generation with both Oracle SEQUENCES and SQL-Server IDENTITIES
- How to return value from stored procedure using PDO on MSSQL database
- Building a custom SQL querying tool that doesn't require the user to know SQL (ASP.NET)
- How do database naming conventions change for multitenant applications?
- Crystal Reports - Data missing when exporting to (PDF or excel or word)
- Inmemory storageMode for multidimensional cubes
- SQL SELECT incremental batch number every X rows
- How to transpose 2 columns to row
- MSSQL - Invalid column name with Java/JTDS and Join
- SQL Server IDENTITY(1,1) Column not generated in sync with UpdatedDate
- How do I refer to SQL Server database with SqlDataAdapter
- MS SQL QUERY Vs SPARK SQL return difference total of rows
- Is there a way to optimize recursive query?
- Spring Batch Update: Insert only if does not exist otherwise update
- Requests are taking too long ~1s Node Express API (TTFB issue)
- How to query and receive as a result this kind of table?
- Can we force SQL Server for case sensitivity
- Should I use foreign keys?
- Problems with encoding ASP.NET Core & SQL Server
- Tools to generate SQL DDL and Content from an existing database
- Change aggregate functions to output NULL when a element is NULL
- SSIS Select * From (Variable Table) Execute SQL Task
- How to use parameters in the stored procedure to check if a specific xml element has a specific value?
- How to create t-sql to load next n-amount of records?
- Pass datatable as paramater in stored procedure
- SQL count with another table in SELECT clause
- Rename SQL query column names dynamically
- Single default value in a table
- How to install Visual Studio 2008 and MS SQL Server 2008 in Ubuntu?
- Generalize SQL query to include data for all Users
- Can I set ignore_dup_key on for a primary key?
- update query works as I expect in SQL Server, but not in Postgresql
- Setting a DataRow item to null
- Keep getting an error: Procedure or function 'usp_StoredProcName' expects parameter '@inputVal', which was not supplied
- Replicating multiple servers with specific replication requirements between servers
- How to count the number of rows in a dimension using mdx (SSAS)
- How can use current day as column name in SQL?
- Use the option IsolationLevel.ReadUncommited for only one query in DbContext
- SQL query - matching data between two tables
- Join two tables on matching ID?
- Logic to check if exact ids (3+ records) are present in a group in SQL Server
- Pagination in a SQL Server stored procedure with duplicated data
- Display few specific rows always at the top
- Remove permission from public
- Join two temp tables
- Start local server automatically
- getting the right value using MAX() on SQL
- Unused SP, Tables list from SQLSERVER 2008
- Dynamic SSRS LInk
- Convert varchar of HH:MM:SS to minutes value
- How do I get the list of all views present in a database and its record count?
- DBI::dbSendQuery fails with RJDBC::JDBC for SQL SERVER
- Does SQL Server 2000 log queries that produce errors anywhere?
- Get all result options from the table
- how to filter by year in fetchxml
- 'Unable to open the physical file' error trying to attach DB to Microsoft SQL Server on Linux for Docker Engine using mssql-cli
- get only date from sql
- How to enable local (i.e. offline) help in SSMS 2016?
- WCF/Entity Framework with External SQL Server DB Does Not Work when Deployed to IIS
- How to find the names of recently dropped functions?
- How to group-concatenate multiple columns?
- Prevent insertion of duplicates
- Determine which columns have changed in rows of a table
- No backupset selected to be restored SQL Server 2016
- Need advice on solving an Invalid column name error
- Saving an image to SQL Server 2008?
- SQL : How do i output date range in SQL Server
- DELETE FROM WHERE IN, but with 2 fields and no Id
- Unclear which type of lock I require to avoid duplicate INSERTS on unique field
- Configuring multiple azure sql databases in spring boot
- how to use sum and max in one query
- Is there a better, more optimized way to perform this SQL query?
- Why i am getting Server error 500 when i upload my app to server
- How to create a relationship with a table which has a composite primary key?
- How to GROUP BY with an exception?
- SQL - Max no of UNIQUE Constraint per table?
- calculate left and right children count in binary tree in sql server 2008 r2
- T-SQL - Split the next word after two specific words
- SQL string split and value increment
- Alter column which is a composite key-Sql Server
- SQL statement with CASE WHEN in ORDER BY causes type convert error
- How do I connect to SQL Server using OLEDB using Windows Authentication connection string
- Select top 2 distinct for each id and date
- Why there are multiple storage motors in MySQL
- Does anyone have a WQL Query for finding the Edition of a SQL Server Instance?