关于一个分级汇总

关于一个分级汇总的问题..
有多少级是不确定的,

ID  PID    Name
1    null    A
2   1       B
3    1      C
4   3       D
5   4       E
: :  :

要求结果为:

ID  Name1 Name2 Name3 Name4 ....
1    A
2    A        B
3    A        C
4    A        C      D
5    A        C      D        E

 

---------------------------------

--  Author: liangCK 小梁

---------------------------------

 

--> 生成测试数据: @T

CREATE TABLE T(ID INT,PID INT,Name VARCHAR(1))

INSERT INTO T

SELECT 1,null,'A' UNION ALL

SELECT 2,1,'B' UNION ALL

SELECT 3,1,'C' UNION ALL

SELECT 4,3,'D' UNION ALL

SELECT 5,4,'E'

 

--SQL查询如下:

--2005写法

DECLARE @i INT

 

;WITH Liang AS

(

    SELECT

        *,

        PATH=CAST(Name AS VARCHAR(MAX))

    FROM T

    WHERE PID IS NULL

    UNION ALL

    SELECT

        B.*,

        A.PATH+'.'+B.Name

    FROM Liang AS A

        JOIN T AS B

            ON B.PID=A.ID

)

SELECT @i=MAX(LEN(PATH)-LEN(REPLACE(PATH,'.','')))+1

FROM Liang

 

DECLARE @SQL VARCHAR(MAX)

DECLARE @n INT

SELECT @SQL='',@n=1

 

WHILE @n<=@i

BEGIN

    SELECT @SQL=@SQL+',xml_data.value(''(/v)['+RTRIM(@n)+']'',''varchar(20)'') AS [Name'+RTRIM(@n)+']'

   

    SET @n=@n+1

END

 

EXEC('

    ;WITH Liang AS

    (

        SELECT

            *,

            PATH=CAST(Name AS VARCHAR(MAX))

        FROM T

        WHERE PID IS NULL

        UNION ALL

        SELECT

            B.*,

            A.PATH+''.''+B.Name

        FROM Liang AS A

            JOIN T AS B

                ON B.PID=A.ID

    )

    SELECT

        ID'+@SQL+'

    FROM (

        SELECT *,

            xml_data=CONVERT(xml,''<v>''+REPLACE(PATH,''.'',''</v><v>'')+''</v>'')

        FROM Liang

    ) AS A

')

 

DROP TABLE T

 

 

--2000写法

--分段截取函数

CREATE FUNCTION dbo.f_GetStr(

@s varchar(8000),      --包含多个数据项的字符串

@pos int,             --要获取的数据项的位置

@split varchar(10)     --数据分隔符

)RETURNS varchar(20)

AS

BEGIN

    IF @s IS NULL RETURN(NULL)

    DECLARE @splitlen int

    SELECT @splitlen=LEN(@split+'a')-2

    WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0

        SELECT @pos=@pos-1,

            @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')

    RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))

END

GO

 

 

--查询指定节点及其所有子节点的函数

CREATE FUNCTION f_Cid(@PID INT)

    RETURNS @t_Level TABLE(ID INT,PID INT,Name VARCHAR(20),Level int,PATH VARCHAR(8000))

AS

BEGIN

    DECLARE @Level int

    SET @Level=1

    INSERT @t_Level

        SELECT ID,PID,Name,@Level,Name

        FROM T

        WHERE PID=@PID

            OR (@PID IS NULL AND PID IS NULL)

           

    WHILE @@ROWCOUNT>0

    BEGIN

        SET @Level=@Level+1

        INSERT @t_Level

            SELECT a.ID,a.PID,a.Name,@Level,B.PATH+'.'+a.Name

            FROM T a,@t_Level b

            WHERE a.PID=b.ID

                AND b.Level=@Level-1

    END

    RETURN

END

GO

 

DECLARE @i INT

 

SELECT @i=MAX(LEN(PATH)-LEN(REPLACE(PATH,'.','')))+1

FROM dbo.f_Cid(NULL)

 

DECLARE @SQL VARCHAR(8000)

DECLARE @n INT

 

SELECT @SQL='',@n=1

 

WHILE @n<=@i

BEGIN

    SELECT @SQL=@SQL+',dbo.f_GetStr(PATH,'+RTRIM(@n)+',''.'') AS [Name'+RTRIM(@n)+']'

    SET @n=@n+1

END

 

EXEC ('

    SELECT

        ID'+@SQL+'

    FROM dbo.f_Cid(NULL) AS A

')

GO

DROP FUNCTION dbo.f_GetStr,dbo.f_Cid

DROP TABLE T

 

 

/*

ID          Name1                Name2                Name3                Name4

----------- -------------- ------------ ----------------------------

1           A                    NULL                 NULL                 NULL

2           A                    B                    NULL                 NULL

3           A                    C                    NULL                 NULL

4           A                    C                    D                    NULL

5           A                    C                    D                    E

 

(5 行受影响)

*/