怎么将有规则的编码转换为ip和pid的树形结构数据

如何将有规则的编码转换为ip和pid的树形结构数据
如

code

1

1.1

1.1.1

1.1.2

2

2.1

...

结果

code           id      pid

1                 1

1.1              w        1

1.1.1           z        w

1.1.2           4        w

2                 a       

2.1              b        a

...

即将有规则的code（以.为分隔层次），转换为id和pid，id值没有关系可以是guid，pid即父id指向正确即可。
请大家帮忙看看，谢谢。
------解决思路----------------------

WITH table1(code)AS(

    SELECT '1' UNION ALL

    SELECT '1.1' UNION ALL

    SELECT '1.1.1' UNION ALL

    SELECT '1.1.2' UNION ALL

    SELECT '2' UNION ALL

    SELECT '2.1' UNION ALL

    SELECT '3' UNION ALL

    SELECT '3.1' UNION ALL

    SELECT '3.2' UNION ALL

    SELECT '3.10'

)

SELECT code,

       NEWID() id

  INTO #a

  FROM table1



;WITH b AS(

        SELECT c.*,

               p.id pid,

               CASE WHEN p.id IS NULL THEN

                    CONVERT(int, c.code)

               ELSE

                    CONVERT(int,

                            SUBSTRING(c.code,

                                      p.pLen+2,

                                      CHARINDEX('.', c.code+'.', p.pLen+2)-p.pLen))

               END subNum

          FROM #a c

   OUTER APPLY (

                    SELECT TOP 1 *,

                           LEN(a.code) pLen

                      FROM #a a

                     WHERE a.code <> c.code

                       AND c.code LIKE a.code+'.%'

                  ORDER BY a.code DESC

               ) p

)

    SELECT code,

           id,

           pid,

           ROW_NUMBER() OVER(PARTITION BY pid ORDER BY subNum) sort

      FROM b

  ORDER BY pid,subNum

code  id                                   pid                                                  sort

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

1     B8D44A7D-0FF2-4D6C-903F-AFE978EFB88B NULL                                                    1

2     A8CEFEE8-BF27-44BF-9584-79120FB7128E NULL                                                    2

3     C1CDE146-4C8F-401A-B6C7-34D0654EE3C7 NULL                                                    3

3.1   32203B46-23A8-4425-B2F8-E6F47A6839F9 C1CDE146-4C8F-401A-B6C7-34D0654EE3C7                    1

3.2   5C389941-FEAE-4C65-B309-A863732E4C5C C1CDE146-4C8F-401A-B6C7-34D0654EE3C7                    2

3.10  DCC3C204-CC95-4021-AD78-CEC216569D6D C1CDE146-4C8F-401A-B6C7-34D0654EE3C7                    3

1.1.1 752A9CC7-41B6-4EE3-A6B6-7279D1BBC38A B2BAAE92-5EEC-4E31-AB23-40F09229114B                    1

1.1.2 8CD587F2-331B-4B3B-9D20-261CE6E63949 B2BAAE92-5EEC-4E31-AB23-40F09229114B                    2

2.1   94A0AFF0-0E95-45E9-8ACF-486A58C026CD A8CEFEE8-BF27-44BF-9584-79120FB7128E                    1

1.1   B2BAAE92-5EEC-4E31-AB23-40F09229114B B8D44A7D-0FF2-4D6C-903F-AFE978EFB88B                    1