SQL 递归算法 行转列

写SQL 根据要求需要取分类的头四级显示出来,分类无极数递归

例如: 手机->三星->盖世兔->1

             ->HTC->G系统->G14->G14简版

             ->HTC->G系统->G14->G14起航版->..... 无数级

       电脑->三星->R系列->1

             ->惠普->G系统->G14->G14简版

             ->IBM->G系统->G1->G1起航版->..... R40

要求根据最底层的求出前四级显示

R40已经 返回  电脑 IBM G系统 G1

ALTER FUNCTION [dbo].[fn_RefCategorys] (@PCID int)
RETURNS @RefValueCategorys TABLE
(
    one int,
    two int,
    three int,
    four int
)
AS
begin
;with hgo as
(
   select PCID,ParentID,0 as rank,0 as id from [ProductCategorys] where PCID=@PCID
   union all
   select h.PCID,h.ParentID,h1.rank+1,0 as id from [ProductCategorys] h join hgo h1 on h.PCID=h1.ParentID
)

insert into @RefValueCategorys
select
IsNULL(max(case t.rank when b.aa-1 then t.PCID end),0) as one,
IsNULL(max(case t.rank when b.aa-2 then t.PCID end),0) as two,
IsNULL(max(case t.rank when b.aa-3 then t.PCID end),0) as three,
IsNULL(max(case t.rank when b.aa-4 then t.PCID end),0) as fist
from hgo t ,(select COUNT(1) as aa from hgo) b
group by t.id;

return
end