You must Sign In to post a response.
  • Category: SQL Server

    Using pivot table with column Wise totals in sql server

    I have created a pivot table with the following code: I would like to add grant total row wise any help?
    CREATE TABLE #TEMP
    (
    [BillNo] nvarchar(100) NULL,
    [InvoiceDate] datetime NULL,
    [InvoiceNo] nvarchar(100) NULL,
    [AccountName] varchar(100) NULL,
    [FormName] varchar(100)NULL,
    [TIN] nvarchar(100),
    [CST] varchar(10),
    [TaxRate]numeric(18,2),
    [TaxAmount]numeric(18,2),
    [Issue] numeric(18,2)
    )

    INSERT INTO #TEMP
    Select BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate,Sum(TaxAmount),Sum(Issue) FROM Stocks where FormName='PU' Group By BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate
    UNION
    Select BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate,SUM(TaxAmount),SUM(Issue) FROM Stocks where FormName='Cpur'Group By BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate




    DECLARE @cols AS NVARCHAR(MAX),@colsName as NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)


    select @cols = STUFF((SELECT Distinct ',' + QUOTENAME(convert(nvarchar(max),TaxRate)+'_'+c.col)

    from #Temp
    cross apply
    (
    select 'TaxAmount' col
    union all
    select 'Issue'
    ) c

    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    select @colsName= STUFF((SELECT Distinct ',' + QUOTENAME(convert(nvarchar(max),TaxRate)+'_'+c.col)
    +' as ['
    +convert(nvarchar(MAX),TaxRate) + case when c.col = 'TaxAmount' then 'of %TaxAmount]' else '%Amount]' end
    from Stocks
    cross apply
    (
    select 'TaxAmount' col
    union all
    select 'Issue'
    ) c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')




    set @query = 'SELECT BillNo,Convert(nvarchar,InvoiceDate,103) AS BillDate,InvoiceNo,AccountName AS PartyName,FormName,TIN,CST, ' + @colsName + '
    from
    (
    select
    BillNo,
    InvoiceDate,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    convert(nvarchar(max),TaxRate) +''_''+col col,
    value
    from
    (
    select BillNo,
    InvoiceDate ,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    TaxRate,
    TaxAmount TaxAmount,
    Issue Issue
    from #Temp
    ) src
    unpivot
    (
    value
    for col in (TaxAmount, Issue)
    ) unpiv

    ) x
    pivot
    (
    sum(value)
    for col in (' + @cols + ')
    ) p
    Union ALL

    SELECT BillNo,Convert(nvarchar,InvoiceDate,103) AS BillDate,InvoiceNo,AccountName AS PartyName,FormName,TIN,CST, ' + @colsName + '
    From(select
    BillNo,
    InvoiceDate,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    convert(nvarchar(max),TaxRate) +''_''+col col,
    value
    from
    (
    select 0 AS BillNo,
    Null AS InvoiceDate ,
    Null AS InvoiceNo,
    ''TOTAL'' AS AccountName,
    Null As FormName,
    Null As TIN,
    Null As CST,
    TaxRate,
    Sum(TaxAmount) As TaxAmount,
    Sum (Issue) As Issue
    from #Temp Group by TaxRate
    ) src
    unpivot
    (
    value
    for col in (TaxAmount,Issue)
    ) unpiv

    ) x
    pivot
    (
    sum(value)
    for col in (' + @cols + ')
    ) p


    '
    execute(@Query)

    DROP TABLE #Temp
  • #747073
    Can you give the sample data for "Stocks" table?

  • #747085
    Go through the below script let me if u have any issues,

    CREATE TABLE #TEMP
    (
    [BillNo] nvarchar(100) NULL,
    [InvoiceDate] datetime NULL,
    [InvoiceNo] nvarchar(100) NULL,
    [AccountName] varchar(100) NULL,
    [FormName] varchar(100)NULL,
    [TIN] nvarchar(100),
    [CST] varchar(10),
    [TaxRate]numeric(18,2),
    [TaxAmount]numeric(18,2),
    [Issue] numeric(18,2)
    )

    INSERT INTO #TEMP
    Select BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate,Sum(TaxAmount),Sum(Issue) FROM Stocks where FormName='PU' Group By BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate
    UNION
    Select BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate,SUM(TaxAmount),SUM(Issue) FROM Stocks where FormName='Cpur'Group By BillNo,Date,InvoiceNo,AccountName,FormName,TIN,CST,TaxRate




    DECLARE @cols AS NVARCHAR(MAX),@colsName as NVARCHAR(MAX),@cols1 as NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)


    select @cols = STUFF((SELECT Distinct ',' + QUOTENAME(convert(nvarchar(max),TaxRate)+'_'+c.col)

    from #Temp
    cross apply
    (
    select 'TaxAmount' col
    union all
    select 'Issue'
    ) c

    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    select @colsName= STUFF((SELECT Distinct ',' + QUOTENAME(convert(nvarchar(max),TaxRate)+'_'+c.col)
    +' as ['
    +convert(nvarchar(MAX),TaxRate) + case when c.col = 'TaxAmount' then 'of %TaxAmount]' else '%Amount]' end
    from Stocks
    cross apply
    (
    select 'TaxAmount' col
    union all
    select 'Issue'
    ) c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')

    select @cols1= STUFF((SELECT Distinct ',' + QUOTENAME(convert(nvarchar(max),TaxRate)+'_'+c.col)
    from Stocks
    cross apply
    (
    select 'TaxAmount' col
    union all
    select 'Issue'
    ) c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,1,'')


    set @query = '
    select BillNo,BillDate,InvoiceNo,case when dept_order=1 then ''SUB TOTAL'' else PartyName end as PartyName,FormName,TIN,CST, ' + @colsName+ '
    from (
    SELECT BillNo AS BillNo1, t.dept_order,BillNo,Convert(nvarchar,InvoiceDate,103) AS BillDate,InvoiceNo,AccountName AS PartyName,FormName,TIN,CST, ' + @cols1 + '
    from
    (
    select
    BillNo,
    InvoiceDate,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    convert(nvarchar(max),TaxRate) +''_''+col col,
    value
    from
    (
    select distinct BillNo,
    InvoiceDate ,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    TaxRate,
    TaxAmount TaxAmount,
    Issue Issue
    from #Temp
    ) src
    unpivot
    (
    value
    for col in (TaxAmount, Issue)
    ) unpiv

    ) x
    pivot
    (
    sum(value)
    for col in (' + @cols + ')
    ) p
    CROSS join (select 0 as dept_order
    union all
    select 1 as dept_order
    ) AS t


    Union ALL

    SELECT 99999999 AS BillNo1,0 as dept_order,BillNo,Convert(nvarchar,InvoiceDate,103) AS BillDate,InvoiceNo,AccountName AS PartyName,FormName,TIN,CST, ' + @colsName + '
    From(select
    BillNo,
    InvoiceDate,
    InvoiceNo,
    AccountName,
    FormName,
    TIN,
    CST,
    convert(nvarchar(max),TaxRate) +''_''+col col,
    value
    from
    (
    select 0 AS BillNo,
    Null AS InvoiceDate ,
    Null AS InvoiceNo,
    ''GRAND TOTAL'' AS AccountName,
    Null As FormName,
    Null As TIN,
    Null As CST,
    TaxRate,
    Sum(TaxAmount) As TaxAmount,
    Sum (Issue) As Issue
    from #Temp Group by TaxRate
    ) src
    unpivot
    (
    value
    for col in (TaxAmount,Issue)
    ) unpiv

    ) x
    pivot
    (
    sum(value)
    for col in (' + @cols + ')
    ) p
    ) as a
    order by BillNo1,FormName,dept_order

    '
    execute(@Query)

    DROP TABLE #Temp

  • #747188
    Lets make it simple; You can very well refer to this article that has the rich information about pivot in sql server as well as generating grand total using pivot

    http://dotnetbites.com/grand-total-pivot-sql-server

    Please mark this as Answer, if this helps

    Regards,
    Alwyn Duraisingh.M 
    << Database Administrator >>
    Jesus saves! The rest of us better make backups...


  • Sign In to post your comments