Thanks a lot Hugo..It works perfect...

Yes you are correct more than one CTE references cannot be used in the recursive query.

Can you suggest other solution which will work..

Thanks Sergiy !

I have tried to modify the change you mentioned but I got an error..

Msg 253, Level 16, State 1, Line 1

Recursive member of a common table expression 'cteHierarchy'...

Thanks ..I was figuring out the data which has millions of records however I have the data which have mentioned..

What is the solution.. when I put max recursion to 0...

Thanks a lot Hugo Kornelis

However i have ran against the database whenever verified after the results it has lot of duplicate records showing in the result set..

Can you help please..

Thanks a lot..

The provided query works fine however when I ran the query against with 1 million records which is failing due to maximum recursion error and I have added...

Here is the query...

CREATE TABLE #Treedesc

(

ID BIGINT ...

Thanks for the response. This was helpful but my database has 2 tables where the provided the link has a single table which has all the names, ids in single...

Thanks a lot it works...

for id there would be stuname like 100 row value which should be display as columns..

I am working on reports but it is denormalized database..

I have provided sample data but i have 100 colmnns

Thanks the expected output is different....

Yes you are correct the other strings contains different time zone.

Thanks a lot.

How can i extend the query to pull the records fromdate and to date..