sqlserver 字符串截取和递归有关问题

sqlserver 字符串截取和递归问题
问题1 
如下，A为数据库表结构（A的行数不定），想用sql查询出B的结果,即A表的a2列中内容以‘,’分割，并和对应的a1列组成查询结果中的一行。 求sql代码。



问题2
数据库表结果只有两列，要递归查询出全部的层级，求sql实例说明

------解决思路----------------------

if OBJECT_ID('test') is not null
drop table test 
go 
create table test 
( 
    id int, 
    name varchar(10), 
    [key] varchar(20) 
) 
go 
insert test 
select 1,'lisa','li,is,sa' union all
select 2,'sophia','ab,cd,ef' union all
select 3,'lori','12,34,23'
go 
  
select
    id, 
    a.name, 
    SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key] 
from
    test a,master..spt_values 
where
    number >=1 and number<=len([key])  
    and type='p' 
    and substring(','+[key],number,1)=','
/* 
id    name    key
----------------------------- 
1    lisa    li 
1    lisa    is
1    lisa    sa 
2    sophia    ab 
2    sophia    cd 
2    sophia    ef 
3    lori    12 
3    lori    34 
3    lori    23 
*/ 

------解决思路----------------------
第一个问题可以参考http://blog.csdn.net/zwxrain/article/details/2512991
对指定字符串分割
------解决思路----------------------
问题1：

CREATE TABLE #TB1
(
	ID VARCHAR(50),
	VAL VARCHAR(50)
)

INSERT INTO #TB1
        ( ID, VAL )
SELECT 'A','aa,bb,cc'
UNION ALL
SELECT 'B','!!,%%,@@'
UNION ALL
SELECT 'C','EE,aA,123'

SELECT  A.ID,
		B.V		
		FROM (SELECT ID,CONVERT(XML,'<root><v>' + REPLACE(Val,',','</v><v>')+'</v></root>') AS Val FROM #TB1) A
		OUTER APPLY (
		SELECT t.c.value('.','varchar(50)') AS V
			FROM A.Val.nodes('/root/v') AS t(c)) B

------解决思路----------------------
问题2

cte(leaf,ID,PID,levelR) AS (
    --从叶子开始递归
    SELECT ID,ID,PID,1
      FROM digui
     WHERE NOT EXISTS (SELECT *
                         FROM digui c
                        WHERE c.PID = digui.ID)
    --向上递归
    UNION ALL
    SELECT c.leaf,p.ID,p.PID,c.levelR+1
      FROM digui p
      JOIN cte c ON p.ID = c.PID
    --加根节点。如果有 ('zgID',NULL) 这样的记录，下面就不需要了。
    UNION ALL
    SELECT c.leaf,c.PID,NULL,c.levelR+1
      FROM cte c
     WHERE c.PID IS NOT NULL
       AND NOT EXISTS (SELECT *
                         FROM digui p
                        WHERE p.ID = c.PID)
)
,t1 (leaf,Levels) AS (
    SELECT leaf, MAX(levelR)
      FROM cte
  GROUP BY leaf
)
,t2 (leaf,ID,level) AS (
    SELECT c.leaf, c.ID, t1.Levels-c.levelR+1
      FROM cte c
      JOIN t1 ON t1.leaf = c.leaf
)
SELECT [1], [2], [3], [4]
  FROM t2
 PIVOT (
        MAX(ID)
        FOR LEVEL IN ([1], [2], [3], [4])
      ) AS p