c# - 带数据透视表的CTE

我有一张桌子：TblMemberPosition

运行SQL代码-

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 <= 8
    Group by Level
    order by Level

以上查询结果：

MemberCount Level
1             1
1             2
3             3
1             4
2             5
1             6
1             7
2             8

但是我想要输出像-

MemberID    Level1   Level 2    Level 3  Level 4 .... Level upto 9
10021        1         1          3        1

此代码是为单个MemberId生成的。您能帮助为所有成员生成此结构吗？

最佳答案

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
 ),
ctePivotSource as (
   select
       (count (MemberID)) as MemberCount,
       'Level ' + convert(varchar(1),[Level]) as [Level]
   from cte
   where Level <=8
   group by level
)
select *
from
    ( select [MemberID],[MemberCount],[Level] from ctePivotSource) src
pivot
(
  sum(src.MemberCount) FOR [Level] in ([Level 1],[Level 2],[Level 3],[Level 4],[Level 5],[Level 6],[Level 7],[Level 8])
) as pvt

您的原始查询将列限制为8列（而不是所需结果中的9列），因此我也将数据透视表限制为8列。

关于c# - 带数据透视表的CTE，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37906211/