--1
if object_id('[a]') is not null drop table [a]
go
create table [a]([code] varchar(8),[value] int,[lev] int)
insert [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
go

--更新
update t
set value=(select sum(value) from a where code like t.code+'%')
from a t
where value is null
go

--结果
select * from a
/**
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

(8 行受影响)
**/


--2
if object_id('[b]') is not null drop table [b]
go
create table [b]([code] varchar(2),[val1] int,[val2] int,[val3] int)
insert [b]
select '01',10,20,30 union all
select '02',11,12,13 union all
select '03',101,88,0
go

--查询
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) t
exec ('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          101
val2 20          12          88
val3 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