表的转换(BOM展开问题)

Question

表1 表2
PIC CID USAGE_AMOUNT PIC CID USAGE_AMOUNT
A B 2 A B 2
A C 3 A C 3
A D 5 A D 5
B C 3 A C 6
B E 8 A E 16
E F 2 A F 32
E G 3 A G 48请教大虾如何把表1转换为表2，网上搜了很多，可是研究了一天也没成功，比较急，只好求助高手了！

Answer 1

----------------实例------------------------
CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GOCREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID INT
    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
    BEGIN
        SET @ID=@PID
        SELECT @PID=PID FROM BOM WHERE ID=@ID
    END
    RETURN @PID
END
GOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO/*
PID         ID
----------- ----------- 
801         101
801         102
801         103
801         601
801         101
801         105
801         501
801         106
801         121
*/

Answer 2

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-22 12:29:44
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([PIC] NVARCHAR(10),[CID] NVARCHAR(10),[USAGE_AMOUNT] INT)
INSERT [tb]
SELECT 'A','B',2 UNION ALL
SELECT 'A','C',3 UNION ALL
SELECT 'A','D',5 UNION ALL
SELECT 'B','C',3 UNION ALL
SELECT 'B','E',8 UNION ALL
SELECT 'E','F',2 UNION ALL
SELECT 'E','G',3
GO
--SELECT * FROM [tb]-->SQL查询如下:
;with t as
(
select PIC1=PIC,*,USAGE_AMOUNT1=USAGE_AMOUNT from tb t where not exists(select 1 from tb where t.pic=cid)
union all
select b.PIC1,a.*,USAGE_AMOUNT1*a.USAGE_AMOUNT from tb a join t b on b.[CID]=a.pic
)
select PIC1 PIC,CID,USAGE_AMOUNT1 USAGE_AMOUNT from t
/*
PIC        CID        USAGE_AMOUNT
---------- ---------- ------------
A          B          2
A          C          3
A          D          5
A          C          6
A          E          16
A          F          32
A          G          48(7 行受影响)
*/

Answer 3

declare @lvl int;
set @lvl=0
insert #tt1
select PIC as PIC1,PIC,CID,USAGE_AMOUNT*1 as USAGE_AMOUNT,@lvl as lev  from   #tt where PIC='A';while @@ROWCOUNT>1
begin
set @lvl=@lvl+1insert #tt1
select a.PIC1,b.PIC,b.CID,a.USAGE_AMOUNT*b.USAGE_AMOUNT ,lvl+1
from #tt1 a,#tt b where a.CID=b.PIC and a.lvl=@lvl-1
endselect PIC1,CID,USAGE_AMOUNT from #tt1PIC1       CID        USAGE_AMOUNT
---------- ---------- ------------
A          B                     2
A          C                     3
A          D                     5
A          C                     6
A          E                    16
A          F                    32
A          G                    48(7 行受影响)

Answer 4

htl258(Tony)
谢谢你的回答，我试了，在SQL 2005中可以通过，不过我的一台服务器是SQL 2000，能给出2000里的代码吗？

Answer 5

--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([PIC] NVARCHAR(10),[CID] NVARCHAR(10),[USAGE_AMOUNT] INT)
INSERT [tb]
SELECT 'A','B',2 UNION ALL
SELECT 'A','C',3 UNION ALL
SELECT 'A','D',5 UNION ALL
SELECT 'B','C',3 UNION ALL
SELECT 'B','E',8 UNION ALL
SELECT 'E','F',2 UNION ALL
SELECT 'E','G',3
GO
--SELECT * FROM [tb]DECLARE @T TABLE(PIC1 VARCHAR(10),PIC VARCHAR(10),CID VARCHAR(10),USAGE_AMOUNT INT,LVL INT)
DECLARE @LVL INT;
SET @LVL=0
INSERT @T
SELECT PIC1=PIC,PIC,CID,USAGE_AMOUNT*1,@LVL FROM TB T
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE T.PIC=CID)
WHILE @@ROWCOUNT>0
BEGIN
SET @LVL=@LVL+1
INSERT @T
SELECT A.PIC1,B.PIC,B.CID,A.USAGE_AMOUNT*B.USAGE_AMOUNT,A.LVL+1
FROM @T A JOIN TB B ON A.CID=B.PIC AND A.LVL=@LVL-1
END
SELECT PIC1,CID,USAGE_AMOUNT FROM @T
/*
PIC1       CID        USAGE_AMOUNT
---------- ---------- ------------
A          B          2
A          C          3
A          D          5
A          C          6
A          E          16
A          F          32
A          G          48(7 行受影响)
*/