Pivot query with Row total and column total

Question

Hi,
I need pivot with Row total and Column total
Rowtotal = without DeductAmt column

create table #temp(Tags varchar(20))
insert into #temp(Tags) values ('Salary'),('HRA'),('Allow'),('DeductAmt')

create table #temp1 (Ledger INT,Tags varchar(20),Amount Decimal(18,2))
insert into #temp1 (Ledger,Tags,Amount)
values
(1001,'Salary','25000'),(1001,'HRA','2000'),(1001,'Allow','1000'),(1001,'DeductAmt','500')
,(1002,'Salary','5000'),(1001,'HRA','250'),(1001,'Allow','100'),(1001,'DeductAmt','200')
,(1003,'Salary','8000'),(1001,'HRA','500'),(1001,'Allow','1500'),(1001,'DeductAmt','700')

--select

drop table #temp,#temp1

I need exact result like below attached image.

Answer 1

--How about this:
create table #temp(Tags varchar(20))
insert into #temp(Tags) values ('Salary'),('HRA'),('Allow'),('DeductAmt'),('Total')

create table #temp1 (Ledger varchar (20),Salary Decimal(18,2),HRA Decimal(18,2), Allow Decimal(18,2), Amount Decimal(18,2),
Total Decimal(18,2))
insert into #temp1 (Ledger,Salary,HRA,Allow, Amount, Total )
values
('1001','25000','2000','1000' ,'500', 25000+2000+1000)
,('1002','500','250','100','200', 500+250+100)
,('1003','8000','500','1500','700',8000+500+1500)
,('Total',25000+500+8000,2000+250+500,1000+100+1500,500+200+700,25000+2000+1000+500+250+100+8000+500+1500)

SELECT * FROM #temp
select * FROM #temp1

drop table #temp,#temp1

Answer 2

SELECT  Ledger,
        SUM(CASE Tags WHEN 'Salary'    THEN Amount END) AS Salary,
        SUM(CASE Tags WHEN 'HRA'       THEN Amount END) AS HRA,
        SUM(CASE Tags WHEN 'Allow'     THEN Amount END) AS Allow,
        SUM(CASE Tags WHEN 'DeductAmt' THEN Amount END) AS DeductAmt,
        SUM(CASE WHEN Tags <> 'DeductAmt' THEN Amount END) AS Total
FROM    #temp1
GROUP   BY Ledger

Answer 3

Hi @Mohamed Farook
If my comment above is right ,then please check the query below:

--Here I modified your sample datas based on your result  
insert into #temp1 (Ledger,Tags,Amount) values  
(1001,'Salary','25000'),(1001,'HRA','2000'),(1001,'Allow','1000'),(1001,'DeductAmt','500')  
,(1002,'Salary','500'),(1002,'HRA','250'),(1002,'Allow','100'),(1002,'DeductAmt','200')  
,(1003,'Salary','8000'),(1003,'HRA','500'),(1003,'Allow','1500'),(1003,'DeductAmt','700')  
  
--query  
 SELECT  ISNULL(CAST(Ledger AS VARCHAR),'Total') AS Ledger,  
         SUM(CASE Tags WHEN 'Salary'    THEN Amount END) AS Salary,  
         SUM(CASE Tags WHEN 'HRA'       THEN Amount END) AS HRA,  
         SUM(CASE Tags WHEN 'Allow'     THEN Amount END) AS Allow,  
         SUM(CASE Tags WHEN 'DeductAmt' THEN Amount END) AS DeductAmt,  
         SUM(CASE WHEN Tags <> 'DeductAmt' THEN Amount END) AS Total  
 FROM #temp1  
 GROUP BY GROUPING SETS(Ledger,())  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.