WPF Treeview : Styling and Template Binding using MVVM

Introduction

In Development world sometimes we have requirement to appearance of the control. This article we walk through how to change appearance of Treeview control using Templates and Styles.

We will also focus on MVVM (Model-View-ViewModel) pattern in this article.

Background

First lets look at MVVM.

MVVM (Model-View-ViewModel):

The Model View ViewModel (MVVM) is an architectural pattern used in software engineering that originated from Microsoft which is specialized in the Presentation Model design pattern. The Model-View-ViewModel (MVVM) pattern provides a flexible way to build WPF/Silverlight applications that promotes code re-use, simplifies maintenance and supports testing.

Model/View/ViewModel also relies on one more thing: a general mechanism for data binding.

View

view in MVVM is defined in XAML. it contains visual controls and rich UI using styles and templates and storyboard.View is used for Data binding and displaying data.

ViewModel

Viewmodel is the mediator between view and viewmodel. it has properties, commands and propertychanged notification logic to execute when any property raise or changed. all business logic related to view is written in viewmodel.

Model

model is like a Entity. it defines properties to exposed.

TREEVIEW

Treeview is used to display data in hirerachical series with parent child relationship. one parent node has multiple child nodes.

WPF has HierarchicalDataTemplate to bind treeviewitem in hierarchical data by setting ItemsSource property. you can change style of treeview using custom template style.

Using the code

This examples is for Company has many departments and each department has employees.

we wish to bind departments in parent node and employees for each department in child nodes in Treeview.

Step 1 : Create Models for Department and Employee

public class Employee{

public int EmployeeID { get; set; }

public string EmployeeName { get; set; }

}

public class Department
{
public int DepartmentID { get; set; }
public string DepartmentName { get; set; }
public List<Employee> EmployeeList { get; set; }
} 

Step 2: Create one class for NotifyProperty Change.

ViewModelBase.cs

public abstract class ViewModelBase:INotifyPropertyChanged
{
         #region Constructor
   protected ViewModelBase()  
   {  }   
   #endregion

  #region Events  
  public event PropertyChangedEventHandler PropertyChanged;          
  #endregion

  #region Event Handlers   
  /// <summary>
  /// Get name of property  
  /// </summary>  
  /// <typeparam name="T"></typeparam>         
  /// <param name="e"></param>  
  /// <returns></returns>  
  public static string GetPropertyName(Expression> e)  
  {          
       var member = (MemberExpression)e.Body;        return member.Member.Name;
 }
  /// <summary>
  /// Raise when property value propertychanged or override propertychage         
  /// </summary>  
  /// <typeparam name="T"></typeparam>         
  /// <param name="propertyExpression"></param>  
  protected virtual void RaisePropertyChanged(Expression> propertyExpression)  
     }

       RaisePropertyChanged(GetPropertyName(propertyExpression));  
  }
  /// <summary>         
  /// Raise when property value propertychanged  
  /// </summary>  
  /// <param name="propertyName"></param>         
  protected void RaisePropertyChanged(String propertyName)  
 {      
       PropertyChangedEventHandler temp = PropertyChanged;
       if (temp != null)
              }
   temp(this, new PropertyChangedEventArgs(propertyName));
       }
  }
  #endregion
} 

if we want to notify viewmodels property change then we nust inherit this class in every viewmodel. and override RaisePropertyChanged method to notify property change.

Step 3: Create viewmodels for each models.

• EmployeeViewModel.cs

public class EmployeeViewModel : ViewModelBase
{         
public Employee Employee { get; protected set; }   
      public int EmployeeID
{
  get { return Employee.EmployeeID; }
  set
  {          
     if (Employee.EmployeeID != value)          
     {              
        Employee.EmployeeID = value;              
        RaisePropertyChanged(() => EmployeeID);          
     }
  }
}
public string EmployeeName
{      
    get { return Employee.EmployeeName; }      
    set      
    {                 
         if (Employee.EmployeeName != value)          
         {              
             Employee.EmployeeName = value;              
             RaisePropertyChanged(() => EmployeeName);          
         }
     }
}
private bool _isChecked;
public bool IsChecked  
{      
    get { return _isChecked; }      
    set      
    {                 
        if (_isChecked != value)          
        {             
            _isChecked = value;              
            RaisePropertyChanged(() => IsChecked);          
        }
    }
}
public EmployeeViewModel(Employee employee)
{             
   this.Employee = employee;      
   IsChecked = false;  
}
} 

This viewmodel holds employee model properties and one more prperty is IsChecked.

IsChecked proeprty is used to check/uncheck child tree nodes and holds value of check/uncheck state for current employee of selected department.

In this viewmodel i am binding Employee model properties because i have to set logic when property value is changed.

• DepartmentViewModel.cs

public class DepartmentViewModel:ViewModelBase
{         
public Department Department { get; protected set; }          
private ObservableCollection _employeeCollection;  
public ObservableCollection EmployeeCollection  
{             
  get { return _employeeCollection; }      
  set {          
          if (_employeeCollection != value)
          {              
              _employeeCollection = value;
              RaisePropertyChanged(() => EmployeeCollection);
          }
      }
}
public int DepartmentID
{      
   get { return Department.DepartmentID; }
   set {          
          if (Department.DepartmentID != value)
          {              
              Department.DepartmentID = value;
              RaisePropertyChanged(() => DepartmentID);
          }
       }
}
public string DepartmentName  
{      
   get { return Department.DepartmentName; }
   set {
           if (Department.DepartmentName != value)  
           {            
                 Department.DepartmentName = value;
                 RaisePropertyChanged(() => DepartmentName);
           }
        }
}
private bool _isChecked;
public bool IsChecked  
{      
   get { return _isChecked; }      
   set      
   {                 
        if (_isChecked != value)
        {             
            _isChecked = value;
            RaisePropertyChanged(() => IsChecked);
            OnCheckChanged();
        }
   }
}
private void OnCheckChanged()  
{      
    foreach (EmployeeViewModel employeeViewModel in EmployeeCollection)
    {          
        employeeViewModel.IsChecked = IsChecked;
    }
}
public DepartmentViewModel(Department department) 
{             
  this.Department = department;      
  EmployeeCollection = new ObservableCollection();      
  foreach (Employee employee in Department.EmployeeList)      
  {          
      EmployeeCollection.Add(new EmployeeViewModel(employee));
  }
}
} 

DepartmentViewModel constist Department Model Properties and Collections of EmployeeViewmodel.

why i have create property ObservableCollection EmployeeCollection because ecah department has list of employee so it should contain list of employee.

ObservableCollection provides dynamic data collections and it also provides notification when any items added/deleted/updated.

IsChecked proeprty is used to check/uncheck department.

OnCheckChanged() method is used to check/uncheck all employee when related department is check/uncheck.

Step 4: Create viewmodel for UI page.

CompanyDetailViewModel.cs

public class CompanyDetailViewModel : ViewModelBase
{  
      private ObservableCollection<DepartmentViewModel> _departmentCollection;
public ObservableCollection<DepartmentViewModel> DepartmentCollection
{      
   get { return _departmentCollection; }      
   set
   {          
       if (_departmentCollection != value)
       {           
            _departmentCollection = value;
            RaisePropertyChanged(() => DepartmentCollection);
       }
   }
}
public CompanyDetailViewModel()  
{      
    DepartmentCollection = new ObservableCollection<DepartmentViewModel>();
    List<Department> departmentList = GetDepartmentList();
    foreach (Department department in departmentList)
    {          
         DepartmentCollection.Add(new DepartmentViewModel(department));
    }
}

#region Methods  
List<Employee> GetEmployeeList()  
{      
    List<Employee> employeeList = new List<Employee>();
    employeeList.Add(new Employee() { EmployeeID = 1, EmployeeName = "Hiren" });
    employeeList.Add(new Employee() { EmployeeID = 2, EmployeeName = "Imran" });
    employeeList.Add(new Employee() { EmployeeID = 3, EmployeeName = "Shivpal" });
    employeeList.Add(new Employee() { EmployeeID = 4, EmployeeName = "Prabhat" });
    employeeList.Add(new Employee() { EmployeeID = 5, EmployeeName = "Sandip" });
    employeeList.Add(new Employee() { EmployeeID = 6, EmployeeName = "Chetan" });      
    employeeList.Add(new Employee() { EmployeeID = 7, EmployeeName = "Jayesh" });     
    employeeList.Add(new Employee() { EmployeeID = 8, EmployeeName = "Bhavik" });
    employeeList.Add(new Employee() { EmployeeID = 9, EmployeeName = "Amit" });
    employeeList.Add(new Employee() { EmployeeID = 10, EmployeeName = "Brijesh" });
    return employeeList;  
}
List<Department> GetDepartmentList()  
{      
    List<Employee> employeeList = GetEmployeeList();
    List<Department> departmentList = new List<Department>();
    departmentList.Add(new Department() { DepartmentID = 1, DepartmentName = "Mocrosoft.Net",
         EmployeeList = employeeList.Take(3).ToList() });
    departmentList.Add(new Department() { DepartmentID = 2, DepartmentName = "Open Source",
         EmployeeList = employeeList.Skip(3).Take(3).ToList() });
    departmentList.Add(new Department() { DepartmentID = 3, DepartmentName = "Other",
          EmployeeList = employeeList.Skip(6).Take(4).ToList() });
    return departmentList;  
}
#endregion
}

this viewmodel is for CompanyDetailView.xaml page. declared ObservableCollection DepartmentCollection property to holds collection of departments to bind in treeviewitems.

created two methods for Getting list of Departmetns and List of Employee and bind to each viewmodel propeties.

Step 5: Create XAML page to display treeview

CompanyDetailView.xaml

 <Grid Grid.Row="1"
 MaxHeight="250">
<TreeView ScrollViewer.VerticalScrollBarVisibility="Auto"
 BorderThickness="0"
Background="#FFF"
 ItemContainerStyle="{DynamicResource TreeViewItemStyle}"
 ItemsSource="{Binding DepartmentCollection}"
 ItemTemplate="{DynamicResource DepartmentTemplate}" />
</Grid> 

put Treeview control in page and setting ItemsSource to DepartmentCollection property of CompanyDetailViewModel.

setting ItemContainerStyle to TreeViewItemStyle created in resourcedictionary.

setting ItemTemplate to DepartmentTemplate i.e. template created in resourcedictionary.

Step 6: Create TreeViewItemStyle style

 <Style x:Key="ExpandCollapseToggleStyle"
TargetType="{x:Type ToggleButton}">
<Setter Property="Focusable"
 Value="False" />  
<Setter Property="Width"
 Value="17" /> 
<Setter Property="Height"  Value="17" />
<Setter Property="Template">      
<Setter.Value>
          <ControlTemplate TargetType="{x:Type ToggleButton}">
      <Border Width="17" Height="17"  Background="Transparent">
          <Border Width="17"  Height="17"  SnapsToDevicePixels="true"
              Background="{DynamicResource {x:Static SystemColors.WindowBrushKey}}"
              BorderBrush="{DynamicResource {x:Static SystemColors.GrayTextBrushKey}}"
              BorderThickness="1">
              <Grid>
                        <Rectangle>
                               ~SPECIAL_REMOVE!#~~lt;Rectangle.Fill>
                    <LinearGradientBrush EndPoint="0,1"
                    StartPoint="0,0">
                    <GradientStop Color="#7FD4FF"
                    Offset="0" />
                    <GradientStop Color="#00AAFF"
                    Offset="1" />
                           </LinearGradientBrush>
                    </Rectangle.Fill>
                    </Rectangle>  
                    <Line Stroke="#112844"
                    x:Name="ExpandImg"
                    StrokeThickness="1"   
                    X1="8"
                    Y1="2"
                    X2="8"
                    Y2="14" />
                    <Line Stroke="#112844"
                    StrokeThickness="1"
                    X1="2"
                    Y1="8"
                    X2="14"
                    Y2="8" />  
             </Grid>
          </Border>
    </Border>
      <ControlTemplate.Triggers>
 <Trigger Property="IsChecked"
   Value="True">
   <Setter Property="Visibility"
           TargetName="ExpandImg"   Value="Collapsed" />
      </Trigger>
</ControlTemplate.Triggers>
      </ControlTemplate>
    </Setter.Value>
</Setter>
</Style> 

In above code i have created style for toggle button (Expand/Collapsed) in treeview.

<Rectangle> control used to craete square

<Line> controls is used to create + (Collapsed) and - (Expand) symbol.

<Trigger> is for change state of toggle button when expand/collapsed.

<Style x:Key="TreeViewItemStyle"
TargetType="{x:Type TreeViewItem}">
    <Setter Property="IsExpanded"
 Value="{Binding IsExpanded, Mode=TwoWay}" />
<Setter Property="Background" Value="Transparent" />
    <Setter Property="HorizontalContentAlignment"
Value="{Binding HorizontalContentAlignment,
 RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" />         
<Setter Property="VerticalContentAlignment"
Value="{Binding VerticalContentAlignment,
   RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" />
<Setter Property="Padding"  Value="1,0,0,0" />
    <Setter Property="Foreground"
Value="{DynamicResource {x:Static SystemColors.ControlTextBrushKey}}" />
<Setter Property="FocusVisualStyle"
Value="{StaticResource TreeViewItemFocusVisual}" />
<Setter Property="Template">
 <Setter.Value>
           <ControlTemplate TargetType="{x:Type TreeViewItem}">
        <Grid>
            <Grid.ColumnDefinitions>
                   <ColumnDefinition MinWidth="19"
                        Width="Auto" />
            <ColumnDefinition Width="Auto" />
            <ColumnDefinition Width="*" />
            </Grid.ColumnDefinitions>
            <Grid.RowDefinitions>
            <RowDefinition Height="Auto" />
            <RowDefinition />   
            </Grid.RowDefinitions>

            <ToggleButton x:Name="Expander"
                ClickMode="Press"  IsChecked="{Binding IsExpanded,
                    RelativeSource={RelativeSource TemplatedParent}}"
                Style="{StaticResource ExpandCollapseToggleStyle}" />
                <Border x:Name="Bd"
                      BorderBrush="{TemplateBinding BorderBrush}"
                      BorderThickness="{TemplateBinding BorderThickness}"
                      Background="{TemplateBinding Background}"
                      Grid.Column="1"    Padding="{TemplateBinding Padding}"
                      SnapsToDevicePixels="true">
                      <ContentPresenter x:Name="PART_Header"
                         ContentSource="Header"
                         VerticalAlignment="{TemplateBinding VerticalContentAlignment}"
                         HorizontalAlignment="{TemplateBinding HorizontalContentAlignment}"
                         SnapsToDevicePixels="{TemplateBinding SnapsToDevicePixels}" />
                   </Border>
                   <ItemsPresenter x:Name="ItemsHost"
                   Grid.ColumnSpan="2"
                          Grid.Column="1"
                   Grid.Row="1" />
          </Grid>
     </ControlTemplate>
 </Setter.Value>
 </Setter>
<Style.Triggers>
 <Trigger Property="VirtualizingStackPanel.IsVirtualizing"  
  Value="true">         
<Setter Property="ItemsPanel">
 <Setter.Value>
   <ItemsPanelTemplate>
        <VirtualizingStackPanel />
   </ItemsPanelTemplate>
</Setter.Value>
</Setter>
</Trigger>  
</Style.Triggers>
</Style> 

Above is style for treeviewitem to set Backgroud color property ,setting controlTemplate, Padding, Alignment etc. properties.

Step 7: Create Templates for display TreeviewItes

 <DataTemplate x:Key="EmployeeDateTemplate">
<StackPanel Orientation="Horizontal">
   <CheckBox Focusable="False"
       IsChecked="{Binding IsChecked,Mode=TwoWay}"
       VerticalAlignment="Center" Margin="5" />
   <TextBlock Text="{Binding EmployeeName}"
          VerticalAlignment="Center" Margin="5" />
</StackPanel>
</DataTemplate>

<HierarchicalDataTemplate x:Key="DepartmentTemplate"
   ItemsSource="{Binding EmployeeCollection}"
ItemTemplate="{StaticResource EmployeeDateTemplate}">
<StackPanel Orientation="Horizontal">
 <CheckBox Focusable="False" Margin="5"
     IsChecked="{Binding IsChecked,Mode=TwoWay}"
     VerticalAlignment="Center" />
 <TextBlock Text="{Binding DepartmentName}"
      Margin="5 5 10 10" />
   </StackPanel>
<HierarchicalDataTemplate>

HierarchicalDataTemplate Template binds data in treeview items as parent nodes and holds child data template. its ItemsSource property holds data of Collection of Employee related to Department.

ItemTemplate property has template for how to display employee data in treeview items. it is DataTemplate for Employee.

Setp 8:Setting DataContext to View

 this.DataContext = new CompanyDetailViewModel(); 

above line ie wriiten in code behind file of MainWindow.xaml to set datacontent of CompanyDetailViewModel which contains Company Data.

At the End put CompanyDetailView.xaml control in MainWindow.xaml and run the code. You Can Download Source From : Download Code

Silverlight Data Binding Using IValueConverter

Introduction

Silverlight is used for creating rich RIA application. if we wish to bind value in XAML, we are using dependencyproperty and dependencyobject.

One more features of Silverlight for data biniding process is IValueConverter.

IValueConverter is used for formatting and binding data to silverlight XAML control.

Background

When we are binding data to controls there will be times when the data needs to be modified.

a value converter is used in that case and you can re-use your source.

In this article we go through creating converter and code for some value converter examples.

Go Through code

Using Value Converter you can convert

- Boolean value to Visibility of control

- Double value to Percentage

- Double to Opacity of Control

- Numeric to Color of Control

Example 1: Numeric to Color of Control

In my XAML page, i am binding Employee List to itemssource and i want to give background color of each employee different display.

First I am creating one converter to convert numeric value to color brush.

Class : NumericToColorConverter.cs

public class NumericToColorConverter : IValueConverter 
{         
public object Convert(object value, Type targetType, object parameter,
        System.Globalization.CultureInfo culture)
{             
  Int32 id = System.Convert.ToInt32(value);          
  LinearGradientBrush brush = new LinearGradientBrush();
  brush.StartPoint = new Point(0, 1);         
  brush.EndPoint = new Point(0, 0);         
  brush.GradientStops.Add(new GradientStop()
    { Color = Colors.White, Offset = 0 });
  brush.GradientStops.Add(new GradientStop()         
    { Color = Color.FromArgb
                 (200,
                   System.Convert.ToByte((id * 103) % 256),
                   System.Convert.ToByte((id * 157) % 256),
                   System.Convert.ToByte((id * 233) % 256)
                  ),
                  Offset = 1
        });         
     return brush;     
 }
 public object ConvertBack(object value, Type targetType, object parameter,
    System.Globalization.CultureInfo culture)     
 {             
     throw new NotImplementedException();         }     }  

IValueConverter has Two methods Convert and ConvertBack to convert passed value.

In Convert method

• first field has value which we passsed from data binding in XAML page.
• second field is parameter passed as ConverterParamter at the time of biding.
• third field is used when we are working on globalization.

you can convert Numeric values to SolidColorBrush or LinearGradientBrush.

Converting numeric value to color you have to convert value to bytes in (A,R,G,B) format that is Red,Green and Blue value and A parameter is for Opacity.

my XAML page :

1. first you need to add reference of converter in usercontrol.

 <UserControl x:Class="MyApp:MyTestPage" 
xmlns:converters="MyApp.Library.Converters"                      
mc:Ignorable="d"> 

2. Create key in resource/style for use in page.

<UserControl.Resources>         
 <converters:NumericToColorConverter x:Key="NumericToColorConverter" />
</UserControl.Resources>

3. Bind Data in Control and pass Converter to convert and get back Background Color.

<ItemsControl x:Name="myControl"       
  VerticalAlignment="Top"
  ItemsSource="{Binding Employees}">
             > <ItemsControl.ItemTemplate> 
                         <DataTemplate>
                           <Border BorderThickness="1"          
                           CornerRadius="5"
                           VerticalAlignment="Top"                            
                           BorderBrush="Black"                            
                           Width="150"                            
                           Height="50"                            
                           Margin="0"                            
                           Background="{Binding EmployeeID,
                             Converter={StaticResource NumericToColorConverter}}">
                            <TextBlock Text="{Binding EmployeeName}"
                             FontWeight="Bold"                                 
                           Height="25" /> 
                     </Border>
          </DataTemplate>                                            
    </ItemsControl.ItemTemplate> 
</ItemsControl> 

so, this way you can convert value to your formatted value.

Example 2 : Boolean to Visibility Converter

In this example you are going to read about, you have boolean value (true/false) based on that you return Visibiliy of control (visible/collapsed) from converter.

public class VisibilityConverter : IValueConverter   
{         
  public object Convert(object value, Type targetType, object parameter,
     System.Globalization.CultureInfo culture)       
  {           
     Boolean result = true;              
     if (parameter != null) Boolean.TryParse(parameter.ToString(), out result);              
     if (value != null && value is Boolean)           
     {                 
        if (System.Convert.ToBoolean(value) == result)               
        {                     
           return System.Windows.Visibility.Visible;               
        }
        else               
        {                   
           return System.Windows.Visibility.Collapsed;               
        }           
     }
     return value;       
  }
  public object ConvertBack(object value, Type targetType, object parameter,
    System.Globalization.CultureInfo culture)       
  {           
      throw new NotImplementedException();       
  }   
}   

In above code if you are passing true value at binding source and pass to converter, it will return Visibility.Visible and it value is false it will return Visibility.Collapsed.

You can also pass converter parameter to get result based on passed parameter.

Creating resource

<UserControl.Resources>
<converters:VisibilityConverter x:Key="VisibilityConverter" />     
</UserControl.Resources> 

In XAML Binding

I am binding IsImageVisible property to True.

  <Border Background="White"                   
 Visibility="{Binding Path=IsImageVisible, Converter={StaticResource VisibilityConverter}}">               
   <Image Stretch="Uniform"                      
 HorizontalAlignment="Center"                      
 VerticalAlignment="Center"                      
 Source="/MyApp;component/Resources/Default/Images/abc.jpg" />
</Border>

so, it will return Visibility to Visible.

Now, Using Converter Parameter

In XAML Binding

I am binding IsImageVisible property to True and ConverterParemter to False.

   <Border Background="White"
      Visibility="{Binding Path=IsImageVisible,
           Converter={StaticResource VisibilityConverter},ConverterParameter=False}">               
      <Image Stretch="Uniform"                      
      HorizontalAlignment="Center"                      
      VerticalAlignment="Center"                      
      Source="/MyApp;component/Resources/Default/Images/abc.jpg" />             
 </Border>

Using above code you will get Visibility to Collapsed.

you can use mulitple functionality by passing converterparameter to converter.

Conclusion

In this article you learned how to use a Value Converter class to take data that is in one format and convert it to another format prior to displaying that value in a XAML control.

Using Common Table Expressions (CTE) in SQL Server

The Common Table Expression (CTE) is called as a temporary named RESULT SET, within the scope of an executing statement that can be used within a SELECT, INSERT, UPDATE, or DELETE or even in a CREATE VIEW or MERGE statement. CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. CTE Syntax:

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT

FROM expression_name;

Simple CTE Example:

To get the product details from the ‘product’ table.

WITH product_CTE
AS
(
   SELECT ProductName, Price, ManufactureDate,ExpirayDate
   FROM dbo.Products

)SELECT * FROM product_CTE;

Recursive CTE Example:

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.

A typical example of hierarchical data is a table that includes a list of employees. For each employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data, as it would appear within the organizational chart.

To demonstrate how the recursive CTE works, I used the following Transact-SQL statements to create and populate the Employees table.

IF OBJECT_ID('Employees', 'U') IS NOT NULL

DROP TABLE dbo.Employees

GO

CREATE TABLE dbo.Employees

(

EmployeeID int NOT NULL PRIMARY KEY,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

ManagerID int NULL

)

GO

INSERT INTO Employees VALUES (101, 'Hiren', 'Khirsaria', NULL)

INSERT INTO Employees VALUES (102, 'Dhaval', 'Dhamsaniya', 101)

INSERT INTO Employees VALUES (103, 'Dharmesh', 'Barochia', 101)

INSERT INTO Employees VALUES (104, 'Chirag', 'Patel', 102)

INSERT INTO Employees VALUES (105, 'Jack', 'Mack', 102)

INSERT INTO Employees VALUES (106, 'Jossef', 'Goldberg', 103)

INSERT INTO Employees VALUES (107, 'Pavan', 'Patel', 103)

INSERT INTO Employees VALUES (108, 'Chetan', 'Virparia', 105)

INSERT INTO Employees VALUES (109, 'Boogi', 'Woogi', 105)

INSERT INTO Employees VALUES (110, 'Abc', 'Xyz', 106)

Now Create CTE for Employee table with select statement to dispaly result of manager for each employee list.

WITH

cteEmployeeHierarchy (EmployeeID, FirstName, LastName, ManagerID, EmployeeLevel)

AS

(

SELECT EmployeeID, FirstName, LastName, ManagerID, 1

FROM Employees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

cteEH.EmployeeLevel + 1

FROM Employees e

INNER JOIN cteEmployeeHierarchy cteEH

ON e.ManagerID = cteEH.EmployeeID

)

SELECT

FirstName + ' ' + LastName AS FullName,

EmployeeLevel ,

(SELECT FirstName + ' ' + LastName FROM Employees

WHERE EmployeeID = cteEmployeeHierarchy.ManagerID) AS Manager

FROM cteEmployeeHierarchy

ORDER BY EmployeeLevel , ManagerID

OUTPUT :

The CTE has enormous capabilities to take over any complex situation.