将一个表行转列,并与另外一个表做计算，再合成新表的SQL.在线等答案,多谢

Question

表A
ORDERID PROID Weight UPrice
001 A 10.521 1.45
001 B 290.000 2.09
001 C 87.154 12.9
002 B 12.220 18.00
003 E 16.997 21.18
表B
ORDERID Caption Price
001 物流费 1200
001 报关费 2000
001 仓储费 800
002 仓储费 200
003 物流费 300
003 报关费 2000
003 仓储费 300形成表C
ORDERID PROID Weight UPrice 物流费 报关费 仓储费
001 A 10.521 1.45 1200*(10.521/(10.521+290.000+87.154)) 2000*百分比 800*百分比
--单据的总物流费是根据总重量来录入的 同物流费
--总物流费*(A的重量除以单据001总重量) 的百分比  
001 B 290.000 2.09 1200*(290.000/(10.521+290.000+87.154)) 2000*百分比 800*百分比
001 C 87.154 12.9 1200*(87.154/(10.521+290.000+87.154)) 2000*百分比 800*百分比
002 B 12.220 18.00 0(无物流费) 0(无报关费) 200*百分比
003 E 16.997 21.18 300*(16.997/(16.997)) 2000*百分比 300*百分比

Answer 1

--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([ORDERID] [nvarchar](10),[PROID] [nvarchar](10),[Weight] [numeric](6,3),[UPrice] [numeric](4,2))
INSERT INTO [A]
SELECT '001','A','10.521','1.45' UNION ALL
SELECT '001','B','290.000','2.09' UNION ALL
SELECT '001','C','87.154','12.9' UNION ALL
SELECT '002','B','12.220','18.00' UNION ALL
SELECT '003','E','16.997','21.18'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([ORDERID] [nvarchar](10),[Caption] [nvarchar](10),[Price] [int])
INSERT INTO [B]
SELECT '001','物流费','1200' UNION ALL
SELECT '001','报关费','2000' UNION ALL
SELECT '001','仓储费','800' UNION ALL
SELECT '002','仓储费','200' UNION ALL
SELECT '003','物流费','300' UNION ALL
SELECT '003','报关费','2000' UNION ALL
SELECT '003','仓储费','300'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
SELECT a.*,
物流费=CAST(CASE SUM([Weight])OVER(PARTITION BY a.[ORDERID]) WHEN 0 THEN 0 
       ELSE ISNULL(物流费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) END AS DEC(18,2)),
报关费=CAST(CASE SUM([Weight])OVER(PARTITION BY a.[ORDERID]) WHEN 0 THEN 0 
   ELSE ISNULL(报关费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) END AS DEC(18,2)),
仓储费=CAST(CASE SUM([Weight])OVER(PARTITION BY a.[ORDERID]) WHEN 0 THEN 0 
   ELSE ISNULL(仓储费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) END AS DEC(18,2))
FROM A AS a
LEFT JOIN (
SELECT * 
FROM b 
PIVOT(
MAX(Price) 
FOR Caption IN(物流费,报关费,仓储费)
) p
) AS b
ON a.ORDERID=b.ORDERID/*
ORDERID    PROID      Weight                                  UPrice                                  物流费                                     报关费                                     仓储费
---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
001        A          10.521                                  1.45                                    32.57                                   54.28                                   21.71
001        B          290.000                                 2.09                                    897.66                                  1496.10                                 598.44
001        C          87.154                                  12.90                                   269.77                                  449.62                                  179.85
002        B          12.220                                  18.00                                   0.00                                    0.00                                    200.00
003        E          16.997                                  21.18                                   300.00                                  2000.00                                 300.00(5 行受影响)
*/

Answer 2

忘了说的是表B的内容是可以增加的，比如客户可以增加一个其他费用，这样就有问题了tony哥能给出这个SQL吗？谢谢

Answer 3

--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
    DROP TABLE [A]
GO
CREATE TABLE [A] ([ORDERID] [nvarchar](10),[PROID] [nvarchar](10),[Weight] [numeric](6,3),[UPrice] [numeric](4,2))
INSERT INTO [A]
SELECT '001','A','10.521','1.45' UNION ALL
SELECT '001','B','290.000','2.09' UNION ALL
SELECT '001','C','87.154','12.9' UNION ALL
SELECT '002','B','12.220','18.00' UNION ALL
SELECT '003','E','16.997','21.18'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
    DROP TABLE [B]
GO
CREATE TABLE [B] ([ORDERID] [nvarchar](10),[Caption] [nvarchar](10),[Price] [int])
INSERT INTO [B]
SELECT '001','物流费','1200' UNION ALL
SELECT '001','报关费','2000' UNION ALL
SELECT '001','仓储费','800' UNION ALL
SELECT '002','仓储费','200' UNION ALL
SELECT '003','物流费','300' UNION ALL
SELECT '003','报关费','2000' UNION ALL
SELECT '003','仓储费','300'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
SELECT a.*,
    物流费=CAST(ISNULL(物流费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) AS DEC(18,2)),
    报关费=CAST(ISNULL(报关费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) AS DEC(18,2)),
    仓储费=CAST(ISNULL(仓储费,0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) AS DEC(18,2))
FROM A AS a
    LEFT JOIN (
        SELECT * 
        FROM b 
            PIVOT(
                MAX(Price) 
                FOR Caption IN(物流费,报关费,仓储费)
            ) p
    ) AS b
        ON a.ORDERID=b.ORDERID/*
ORDERID    PROID      Weight                                  UPrice                                  物流费                                     报关费                                     仓储费
---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
001        A          10.521                                  1.45                                    32.57                                   54.28                                   21.71
001        B          290.000                                 2.09                                    897.66                                  1496.10                                 598.44
001        C          87.154                                  12.90                                   269.77                                  449.62                                  179.85
002        B          12.220                                  18.00                                   0.00                                    0.00                                    200.00
003        E          16.997                                  21.18                                   300.00                                  2000.00                                 300.00(5 行受影响)
*/再简化一下

Answer 4

多谢tony大哥，我现在有个问题是：表B的内容是可以增加的，比如客户可以增加一个其他费用，这样就有问题了tony哥能给出这个SQL吗？谢谢

Answer 5

用动态SQL就能解决了
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
    DROP TABLE [A]
GO
CREATE TABLE [A] ([ORDERID] [nvarchar](10),[PROID] [nvarchar](10),[Weight] [numeric](6,3),[UPrice] [numeric](4,2))
INSERT INTO [A]
SELECT '001','A','10.521','1.45' UNION ALL
SELECT '001','B','290.000','2.09' UNION ALL
SELECT '001','C','87.154','12.9' UNION ALL
SELECT '002','B','12.220','18.00' UNION ALL
SELECT '003','E','16.997','21.18'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
    DROP TABLE [B]
GO
CREATE TABLE [B] ([ORDERID] [nvarchar](10),[Caption] [nvarchar](10),[Price] [int])
INSERT INTO [B]
SELECT '001','物流费','1200' UNION ALL
SELECT '001','报关费','2000' UNION ALL
SELECT '001','仓储费','800' UNION ALL
SELECT '002','仓储费','200' UNION ALL
SELECT '003','物流费','300' UNION ALL
SELECT '003','报关费','2000' UNION ALL
SELECT '003','仓储费','300'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
DECLARE @fee VARCHAR(MAX),@fee1 VARCHAR(MAX)
SELECT @fee=ISNULL(@fee+',','')+QUOTENAME([Caption]),
@fee1=ISNULL(@fee1+',','')+QUOTENAME([Caption])
 +'=CAST(ISNULL('+QUOTENAME([Caption])+',0)*([Weight]/SUM([Weight])OVER(PARTITION BY a.[ORDERID])) AS DEC(18,2))'
FROM B
GROUP BY [Caption]
EXEC('
SELECT a.*,'+@fee1+'
FROM A AS a
LEFT JOIN (
SELECT * 
FROM b 
PIVOT(
MAX(Price) 
FOR Caption IN('+@fee+')
) p
) AS b
ON a.ORDERID=b.ORDERID
')
/*
ORDERID    PROID      Weight                                  UPrice                                  报关费                                     仓储费                                     物流费
---------- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
001        A          10.521                                  1.45                                    54.28                                   21.71                                   32.57
001        B          290.000                                 2.09                                    1496.10                                 598.44                                  897.66
001        C          87.154                                  12.90                                   449.62                                  179.85                                  269.77
002        B          12.220                                  18.00                                   0.00                                    200.00                                  0.00
003        E          16.997                                  21.18                                   2000.00                                 300.00                                  300.00(5 行受影响)
*/

Answer 6

不知还能否问一下，如果再形成一个累计列能否借助SQL来弄，就是形成的C变成这样：
形成表C 
ORDERID PROID Weight UPrice 物流费 报关费 仓储费  费用小计费用小计=物流费+报关费+仓储费+（Weight*UPrice）