The Following Code is used to get the pivoted column from row i need the sum of the each row from the following code Please help me to get the sum of the each row pivoted columns .
PIVOT (MAX([Stock]) FOR [SubInventory] IN ('+STUFF(@columns,1,1,'')+')
bu using this code i got the pivoted columns and i need to get the sum of these column from this code
DECLARE @t TABLE (
id int IDENTITY(1,1),
BranchName nvarchar(max)
)
DECLARE @n int = 0,
@i int = 1,
@BranchName nvarchar(max),
@sql nvarchar(max),
@columns nvarchar(max)
INSERT INTO @t
SELECT DISTINCT BranchName
FROM ALX_Branches
SELECT @n = @@ROWCOUNT
WHILE @n >= @i
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],
[Weighted Average Rate]
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
END
Aucun commentaire:
Enregistrer un commentaire