行列转换问题

[TT008]

已知表A
id Name A B C D E F G H I
1 A 1 2 3 4 5 6 7 8 9
2 B 9 11 22 33 44 55 66 77 88

求表B
id Name 字段A 字段B 字段C 字段D
1 A 1 2 3 4
2 B 9 11 22 33
1 A 5 6 7 8
2 B 44 55 66 77
1 A 9
2 B 88

要求使用动态SQL语句实现

[SQL77]

--> 测试数据: TB
if object_id('TB') is not null drop table TB
create table TB (id int,Name varchar(1),A int,B int,C int,D int,E int,F int,G int,H int,I int)
insert into TB
select 1,'A',1,2,3,4,5,6,7,8,9 union all
select 2,'B',9,11,22,33,44,55,66,77,88

select * from TB


SELECT IDD=IDENTITY(INT,1,1),NAME  INTO #T
FROM SYSCOLUMNS 
WHERE ID=OBJECT_ID('TB') AND NAME NOT IN('ID','NAME')
--SELECT * FROM #T
SELECT
     (IDD-1)/4 AS ID,
      MAX(CASE WHEN (IDD-1)%4=0 THEN NAME  END) AS NAME1,
      MAX(CASE WHEN (IDD-1)%4=1 THEN NAME  END) AS NAME2,
      MAX(CASE WHEN (IDD-1)%4=2 THEN NAME  END) AS NAME3,
      MAX(CASE WHEN (IDD-1)%4=3 THEN NAME  END) AS NAME4 INTO #T2
FROM #T
GROUP BY (IDD-1)/4

DECLARE @S NVARCHAR(4000)
SELECT
	@S=ISNULL(@S+' UNION ALL ','')+'SELECT ID,NAME,'+NAME1+ISNULL(','+NAME2,',0')+ISNULL(','+NAME3,',0')+ISNULL(','+NAME4,',0')+' FROM TB'
FROM 
	#T2

SELECT @S

--DROP TABLE #T,#T2

EXEC('SELECT ID,NAME,A,B,C,D FROM TB
UNION ALL   '+@S)