如何在将行转到Server中的列之后取每一行的和

Question

我发布了我的代码，以便通过使用pivot将行列到列，但我需要获得每个行在pivot之后的和。

BEGIN
    SELECT @BranchName = BranchName
    FROM @t
    WHERE id = @i

    SELECT @columns = (
                        SELECT DISTINCT ','+QUOTENAME([SubInventory])
                        FROM #MyTempTable
                        WHERE [BranchName] = @BranchName
                        FOR XML PATH('')
                      )

    SELECT @sql = N'--
                SELECT   * 
                FROM (
                        SELECT [BranchID],
                               [SubInventory],
                               [Product],
                               [Stock]

                        FROM #MyTempTable
                        WHERE [BranchName] = ''' +@BranchName +''' 
                      ) as t
                PIVOT (

                        MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')
                      ) as pvt'

    EXEC sp_executesql @sql
    SET @i = @i+1

我附加了返回结果的格式，在这张图片中，有两个具有不同分支ID的表，所以我需要产品后面的列名之和

​

​

我需要的结果是

​

​

电流屏

​

​

Answer 1

为了便于评论，我添加了一个答复。

正如我在注释中提到的:如果您需要从其他列中添加一些包含sum的新字段，则需要在数据(如Col1+Col2+Col3.. as [SUM(Col1-Col5)] )中再添加一个变量，如Col1+Col2+Col3.. as [SUM(Col1-Col5)]，然后使用...SELECT '+STUFF(@columns,1,1,'')+@sum +而不是SELECT *。

我发布了您查询的编辑版本。在@SUMcolumns中，我们将存储Col1+Col2+Col3 as [sum]，然后在动态部分中使用它：

SELECT @SUMcolumns = ','+STUFF((
                    SELECT DISTINCT '+COALESCE('+QUOTENAME([SubInventory]) + ', 0)'
                    FROM #MyTempTable
                    WHERE [BranchName] = @BranchName
                    FOR XML PATH('')
                  ),1,1,'')+ ' as [sum]'


SELECT @sql = N'--
            SELECT  [BranchID],
                    [Product]'+
                    @columns+
                    @SUMcolumns
                    +'
            FROM (SELECT * FROM (
                    SELECT [BranchID],
                           [SubInventory],
                           [Product],
                           [Stock]

                    FROM #MyTempTable
                    WHERE [BranchName] = ''' +@BranchName +''' 
                  ) as t
            PIVOT (

                    MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')
                  ) as pvt
           ) as d'

编辑

如果您使用的是Server 2012及以上版本。可以在分区上使用SUM和AVG，如下所示：

DECLARE @SQL nvarchar(max),
        @columns nvarchar(max),
        @BranchName nvarchar(max) = 'Branch1'

SELECT @columns = (
                    SELECT DISTINCT ','+QUOTENAME([SubInventory])
                    FROM #MyTempTable
                    WHERE [BranchName] = @BranchName
                    FOR XML PATH('')
                    )

SELECT @sql = N'
SELECT  [BranchID],
        [Product]
        '+@columns+',
        [SUM Stock],
        [AVG Stock]
FROM (
        SELECT  [BranchID],
                [SubInventory],
                [Product],
                [Stock],
                SUM([Stock]) OVER (PARTITION BY [BranchID], Product) AS [SUM Stock],
                AVG([Stock]) OVER (PARTITION BY [BranchID], Product) AS [AVG Stock]
        FROM #MyTempTable
        WHERE [BranchName] = ''' +@BranchName +''' 
        ) as t
PIVOT (
    MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')
) as pvt'

EXEC sp_executesql @sql

这会给你带来这样的输出：

BranchID    Product         EXB-Teller 3    HOF-Dealer 1    HOF-Remit 1     SUM Stock   AVG Stock
1           Bahraini Dinar  52759.252       25085.631       102.500         77947.383   25982.461000
1           Egiptian Pounds 100.000         100.000         NULL            200.000     100.000000