Pivot to Convert Rows into Columns

 

I need to convert the table as show in the figure above to the one on the right hand side. Using Sql servers Pivot we can easily get this done. Please see the script below:

Get all descendants of a parent using CTE

 

Declare @TblLocations Table(Id Int, Name Varchar(50), ParentId Int)
Insert into @TblLocations(Id, Name, ParentId)
    Values(1, 'India', Null)
Insert into @TblLocations(Id, Name, ParentId)
    Values(2, 'Kerala', 1)    
Insert into @TblLocations(Id, Name, ParentId)
    Values(3, 'Tamil Nadu', 1)        
Insert into @TblLocations(Id, Name, ParentId)
    Values(4, 'Kochi', 2)        
Insert into @TblLocations(Id, Name, ParentId)
    Values(5, 'Chennai', 3)        
    
    
;With Locations As
(
    Select * From @TblLocations
    Where ParentId = 3
    Union All
    
    Select Child.* from @TblLocations Child Inner join Locations Parent on Child.ParentId = Parent.Id
)    
Select * from Locations