jeudi 6 octobre 2016

How can we get the Sum of the Pivot Column sum for each Row?

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