question

Hi, Guys, I am having one sql table in mssql - TblMemberPosition TblMemberPosition having Following Structure - MemberID(int), SponserID(int), MemberName, DateofJoin I am using following CTE query to get depth of tree structure with cte ( MemberID, SponserID, Level ) as ( select MemberID , SponserID , 1 as Level from TblMemberPosition where MemberID = 10021 union all select a.MemberID , a.SponserID , Level + 1 from TblMemberPosition as a inner join cte as b on b.MemberID = a.SponserID ) select ( count(MemberID) ) as MemberCount , Level from cte where Level >= 9 group by Level order by Level; **Query Giving Following Results -** ----------------------------------------------- Count Level 1 1 0 2 5 3 2 4 3 5 10 6 12 7 10 8 104 9 **(1) Please help me to generate query for all members (2) Result Structure should be like this** MemberID Member Name S/D/W of Mobile Level 1 Level 2 Level 3 Level 4 Level 5 Level 6 Level 7 Level 8 10021 Mukesh Kumar Dhakad Devi Lal Ji 9826725116 You 1 3 1 2 1 1 2 10022 RAMPRASAD PRAJAPAT KACHRULAL 9407004175 You 3 1 2 1 1 2 4 10023 ISHWAR GIR GOSWAMI MANGU GIR GOSWAMI 9617899168 You 1 2 1 1 2 4 3 I tried pivot option but giving error. Thanks in advance.