How can we take sum of each row after pivot the rows to column in SQL Server

Question

I posted my code to get the rows to column by using pivot, but I need to get the sum of each row after 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

I attached the format how the result is returned, in this picture two tables are there with different Branch ID, so I need the sum of the column name after products

Current Format

My required result is

Required Format

Current Screen

@pinwar i had attached my required Result Format. here i specified with Col-1 ,col-2 etc , the sum(Col) is my required column(ie, sum of the rest columns) — Dona Susan Issac, Oct 16 '16 at 07:16
@DonaSusanIssac if you need to add some new filed with sum from other columns you need to add one more variable like `@columns` with data like `Col1+Col2+Col3.. as [SUM(Col1-Col5)]` and then use `...SELECT '+STUFF(@columns,1,1,'')+@sum + '` instead of `SELECT *` — gofr1, Oct 16 '16 at 08:01
@ gofr1 , am using the pivot after pivot only i will get my required format after thant only i need to make the sum of the columns — Dona Susan Issac, Oct 16 '16 at 08:05
@DonaSusanIssac Please, specify the exactly version of SQL Server... You have tagged 2005, 2008, 2012. Based on your version - I could suggest more reliable solution to get SUM and AVG that you need — gofr1, Oct 16 '16 at 19:02
@gofr1 i had updated the exact version am using server2012 version — Dona Susan Issac, Oct 17 '16 at 05:33
@DonaSusanIssac That is great! I will add info to my answer in a few minutes! — gofr1, Oct 17 '16 at 05:39

gofr1 · Accepted Answer · 2016-10-17T05:43:32.440

3

To large for comment, so I add an answer.

As I mentioned in comments: if you need to add some new field with sum from other columns you need to add one more variable like @columns with data like Col1+Col2+Col3.. as [SUM(Col1-Col5)] and then use ...SELECT '+STUFF(@columns,1,1,'')+@sum + instead of SELECT *

I post an edited version of your query. In @SUMcolumns we will store the Col1+Col2+Col3 as [sum] and then use it in your dynamic part:

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'

EDIT

If you are using SQL Server 2012 and up. You can use SUM and AVG OVER PARTITION like this:

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

That will bring you output like this:

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

edited Oct 17 '16 at 05:43

answered Oct 16 '16 at 10:28

gofr1

15,741
11
42
52

The column name "HOF-Account 2" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. The column name "HOF-Dealer 1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Msg 265, Level 16, State 1, Line 13 The column 'EXB-Teller 3' was specified multiple times for 'pvt'. – Dona Susan Issac Oct 16 '16 at 11:13
I add some `(...) as d` in my answer, check it now please. – gofr1 Oct 16 '16 at 11:18
now the result is not showing there is not showing the table wise report – Dona Susan Issac Oct 16 '16 at 11:25
Ok, I made your pivot as subquery now. – gofr1 Oct 16 '16 at 11:27
it is similar to old one , subQuery for pivot is not edited – Dona Susan Issac Oct 16 '16 at 11:31
only the first row is showing the rest of it not showing – Dona Susan Issac Oct 16 '16 at 11:38
I put all your pivot into subquery, and then select columns and sum from it. It should work. – gofr1 Oct 16 '16 at 11:44
i did n't understand – Dona Susan Issac Oct 16 '16 at 11:51
Look, you got some PIVOT query, now I make it a subquery and select from it all rows + sum of columns. Sum of columns is generated like `@columbs` variable but `+` instead of `,`. – gofr1 Oct 16 '16 at 11:53
i will input my curent screenshot for ur reference – Dona Susan Issac Oct 16 '16 at 11:54
https://i.stack.imgur.com/EjiqJ.png please refer this link the current image. only the first row showing the sum the rest of rows is not showing – Dona Susan Issac Oct 16 '16 at 11:57
It is because of nulls! Check updated part with `@sumcolumns`. I add `COALESCE` to get rid of nulls. – gofr1 Oct 16 '16 at 12:00
Yes, it can be used to find average, but it will be more complicated... I guess there could be another solution without hardcoding with columns. I will add this to my answer later, I am from my phone right now. – gofr1 Oct 16 '16 at 12:23
ok thank u sir, for ur great help . i think u will upload the code ASA fast for me. – Dona Susan Issac Oct 16 '16 at 12:36
Great work sir, thanks for your help.. now it works for me. thanks a lot. sir – Dona Susan Issac Oct 17 '16 at 06:59
once i come with 15 reputation will up vote your answer sir , – Dona Susan Issac Oct 17 '16 at 06:59
Great that it works fine for you! My pleasure to help! ;) – gofr1 Oct 17 '16 at 07:01

How can we take sum of each row after pivot the rows to column in SQL Server

1 Answers1