This type of report is usually best left to the presentation layer. It's possible to do this in T-SQL, but it's not the best tool for the job.
I completely agree with your opinion. But there are some constraints from Front End and hence I need to resort to T-SQL.
But in case if I had to leave to Presentation Layer to handle the visualization, what should be the Ideal way to give the data? I mean is it sufficient to give the table data as data source or I have to write any query to be able to get the data as expected?
Your solution solves the purpose. Thanks a lot!
I am thinking to create a query which will generate the output in the following way
Probably by creating an Indexed View Keeping the Node Id as Unique, I would get reasonable performance.
I guess the following output can be used by any Front End language to visualize the tree with minimum effort and maximum performance.
NodeIdVisitTypePatientCntParentNodeIdLevelID
1V12NULL1
2V23NULL1
11V1212
12V1122
13V2122
14V3122
111V11113
112V21113
113V41123
114V21133
115V51143
Looking forward for your comments/suggestions.