Rows and Column wise total in sql pivot table

Question

Rows and Column wise total in sql pivot table

akhterhussain80

Mr or Mrs. 500

Points: 504
More actions
May 31, 2019 at 5:59 am

#3648810

i want above result in sql pivot query
here is my data
create table #Containerno(CID int, ContNo varchar(50))
insert into #Containerno
select 1, 'Container(A)', union
select 2, ' Container(B)',
select 3, ' Container(C)',
select 4, ' Container(D)',
select 5, ' Container(E)',
select 6, ' Container(F)',
create table #ConIssuance(IID int, CID int,Iweight int,EntryDate date)
insert into #ConIssuance
select 1, '1', '200','28/5/2019', union
select 2, '2', '200','28/5/2019',
select 3, '3', '200','28/5/2019',
select 4, '4', '300','28/5/2019',
select 5, '5, '100','28/5/2019',
select 6, '6, '200','28/5/2019',
select 7, '1, '100','29/5/2019',
select 8, '2, '100','29/5/2019',
select 9, '3, '50','29/5/2019',
select 10, '4, '50','29/5/2019',
select 11, '5, '50','29/5/2019',
select 12, '6, '50','29/5/2019',

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Here is a query for you. Note that I don't use the PIVOT operator, but instead uses SUM(CASE for the pivoting. This is somewhat more verbose, but also a lot more flexible and adding the sum column is trivial.

To achieve the total row, I use GROUP BY GROUPING SETS.

SELECT  CI.EntryDate, 
        SUM(CASE WHEN CN.ContNo = 'Container(A)' THEN CI.Iweight END) AS [Container(A)],
        SUM(CASE WHEN CN.ContNo = 'Container(B)' THEN CI.Iweight END) AS [Container(B)],
        SUM(CASE WHEN CN.ContNo = 'Container(C)' THEN CI.Iweight END) AS [Container(C)],
        SUM(CASE WHEN CN.ContNo = 'Container(D)' THEN CI.Iweight END) AS [Container(D)],
        SUM(CASE WHEN CN.ContNo = 'Container(E)' THEN CI.Iweight END) AS [Container(E)],
        SUM(CASE WHEN CN.ContNo = 'Container(F)' THEN CI.Iweight END) AS [Container(F)],
        SUM(CI.Iweight) AS Total
FROM    #ConIssuance CI
JOIN    #Containerno CN ON CI.CID  = CN.CID
GROUP   BY GROUPING SETS ((CI.EntryDate), ())
ORDER   BY GROUPING(CI.EntryDate), CI.EntryDate

Note that in pivoting is very much a presentation thing, and therefore also in many cases better handled in the presentation layer. A good reporting tool should have good support for this sort of thing.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Duplicate post. See https://www.sqlservercentral.com/forums/topic/rows-and-column-wise-total-in-sql-pivot-table for answer.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]