求两SQL话语

求两SQL语句
1.表a结构如下：
code value lev
zb null 1
zb.sz null 2
zb.sz.01 11 3
zb.sz.02 10 3
zb.sz.03 9 3
zb.sy null 2
zb.sy.01 5 3
zb.sy.02 4 3
需要更新父级值，得到如下结果
code value lev
zb 39 1
zb.sz 30 2
zb.sz.01 11 3
zb.sz.02 10 3
zb.sz.03 9 3
zb.sy 9 2
zb.sy.01 5 3
zb.sy.02 4 3

2.表b结构如下
code val1 val2 val3
01 10 20 30
02 11 12 13
03 101 88 0
需要行转列如下结果
code 01 02 03
val1 10 11 101
val2 20 12 88
val3 30 13 0

分数不多，求高人

[解决办法]
行转列看这里：http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
[解决办法]

SQL code

--1if object_id('[a]') is not null drop table [a]gocreate table [a]([code] varchar(8),[value] int,[lev] int)insert [a]select 'zb',null,1 union allselect 'zb.sz',null,2 union allselect 'zb.sz.01',11,3 union allselect 'zb.sz.02',10,3 union allselect 'zb.sz.03',9,3 union allselect 'zb.sy',null,2 union allselect 'zb.sy.01',5,3 union allselect 'zb.sy.02',4,3go--更新update tset value=(select sum(value) from a where code like t.code+'%')from a twhere value is nullgo--结果select * from a/**code     value       lev-------- ----------- -----------zb       39          1zb.sz    30          2zb.sz.01 11          3zb.sz.02 10          3zb.sz.03 9           3zb.sy    9           2zb.sy.01 5           3zb.sy.02 4           3(8 行受影响)**/--2if object_id('[b]') is not null drop table [b]gocreate table [b]([code] varchar(2),[val1] int,[val2] int,[val3] int)insert [b]select '01',10,20,30 union allselect '02',11,12,13 union allselect '03',101,88,0go--查询declare @sql varchar(8000)select @sql=isnull(@sql+',','')  +'max(case when code='''+code+''' then val else 0 end) as ['+code+']'from(select distinct code from b) texec ('select c as code,'+@sql+'from   (select code,val1 as val,''val1'' as c from b    union all   select code,val2,''val2'' from b   union all   select code,val3,''val3'' from b  ) t  group by c')--结果/**code 01          02          03---- ----------- ----------- -----------val1 10          11          101val2 20          12          88val3 30          13          0(3 行受影响)**/
[解决办法]
/*
---递归更新父级节点

DECLARE @a TABLE
( code varchar(10), value int, lev int)

INSERT INTO @a
 SELECT 'zb', null , 1
 UNION ALL
 SELECT 'zb.sz', null, 2
 UNION ALL
 SELECT 'zb.sz.01', 11, 3
 UNION ALL
 SELECT 'zb.sz.02', 10, 3
 UNION ALL
 SELECT 'zb.sz.03' ,9, 3
 UNION ALL
 SELECT 'zb.sy' ,null, 2
 UNION ALL
 SELECT 'zb.sy.01', 5, 3
 UNION ALL
 SELECT 'zb.sy.02' ,4, 3  -- 准备测试数据

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp

;WITH c1 AS  --将源表中的父节点展示出来，parent列；（包括根节点）
(
SELECT a.code parent, b.code, b.value, b.lev  
FROM @a a
JOIN @a b ON CHARINDEX(a.Code, b.code) <> 0 AND b.lev - a.lev = 1

UNION ALL
SELECT NULL, a.code, a.value, a.lev 
FROM @a a
WHERE a.lev = 1
)
SELECT *
INTO #temp
FROM c1


DECLARE @level int = ( SELECT MAX(lev) FROM #temp )  -- 找出叶子节点所在的层级

WHILE(@level <> (SELECT MIN(lev) FROM #temp))
 BEGIN
    
   UPDATE t---更新父表t中的内容，t作为父表，c作为子表
   SET t.value = c.SumValue
   FROM #temp t
 INNER JOIN
 (
SELECT parent, SUM(value) SumValue
FROM #temp
WHERE lev = @level
GROUP BY parent
 ) c ON t.code = c.parent
SET @level = @level - 1 
 
    
 END 

  
SELECT *
FROM #temp
ORDER BY lev
**/

----测试结果
parent     code       value       lev
---------- ---------- ----------- -----------
NULL       zb         39          1
zb         zb.sz      30          2
zb         zb.sy      9           2
zb.sz      zb.sz.01   11          3
zb.sz      zb.sz.02   10          3
zb.sz      zb.sz.03   9           3
zb.sy      zb.sy.01   5           3
zb.sy      zb.sy.02   4           3

(8 row(s) affected)


行列互换写不下去了，想睡觉了……