gpt4 book ai didi

sql-server-2012 - 在 SQL Server 中将行转换为列后,我们如何计算每行的总和

转载 作者:行者123 更新时间:2023-12-04 06:55:41 25 4
gpt4 key购买 nike

我发布了我的代码以使用 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

我附上了返回结果的格式,在这张图片中有两个表有不同的 Branch ID,所以我需要 products 之后的列名的总和

Current Format

我要求的结果是

Required Format

当前屏幕

enter image description here

最佳答案

评论太大,所以我添加一个答案。

正如我在评论中提到的:如果您需要从其他列中添加一些新字段和总和,您需要添加一个变量,如 @columns,数据如 Col1+Col2+ Col3.. 作为 [SUM(Col1-Col5)] 然后使用 ...SELECT '+STUFF(@columns,1,1,'')+@sum + 而不是选择 *

我发布了您查询的编辑版本。在 @SUMcolumns 中,我们会将 Col1+Col2+Col3 存储为 [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'

编辑

如果您使用的是 SQL Server 2012 及更高版本。您可以像这样使用 SUM 和 AVG OVER PARTITION:

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

关于sql-server-2012 - 在 SQL Server 中将行转换为列后,我们如何计算每行的总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40067729/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com