Sql join query with total row

Question

Hi , please help

create table #temp (LedgerID INT, Amount Decimal(18,3))

insert into #temp (LedgerID,Amount) values (121,100),(121,250),(129,25),(129,200),(158,100),(158,500),(157,250),(157,100),(48625,1000)

CREATE TABLE #temp_table (

cid INT,

type TEXT,

description TEXT,

ledgers TEXT,

ledger_id TEXT

);

INSERT INTO #temp_table (cid, type, description, ledgers, ledger_id) VALUES

(101, 'Income', 'RevenueAccount', 'WareHouse', '121'),

(101, 'Income', 'AddAccounts', 'Sundry Creditors', '129'),

(101, 'Expense', 'DeductAccounts', 'Shop Rent,Office Rent', '158,157'),

(101, 'Expense', 'Deduction only', 'DE MEGHA FZC', '48625');

select * from #temp

select * from #temp_table

drop table #temp_table

drop table #temp

I need exact result like below

Answer 1

Hi @Mohamed Farook

Try this query:

;WITH CTE1 AS  
(  
 SELECT cid
       ,CONVERT(VARCHAR(50),TYPE) type
	   ,CONVERT(VARCHAR(50),description) description
	   ,VALUE AS ledgers
	   ,ROW_NUMBER()OVER(PARTITION BY cid,CONVERT(VARCHAR(50),TYPE) ORDER BY CONVERT(VARCHAR(50),ledgers)) RNum1  
 FROM #temp_table CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(50),ledgers),',')   
),CTE2 AS  
(  
 SELECT cid
       ,CONVERT(VARCHAR(50),TYPE) type
	   ,CONVERT(VARCHAR(50),description) description
	   ,VALUE AS ledger_id
	   ,ROW_NUMBER()OVER(PARTITION BY cid,CONVERT(VARCHAR(50),TYPE) ORDER BY CONVERT(VARCHAR(50),ledgers)) RNum2  
 FROM #temp_table CROSS APPLY STRING_SPLIT(CONVERT(VARCHAR(50),ledger_id),',')  
)  
SELECT C1.cid,C1.type,C1.description,C1.ledgers,C2.ledger_id,SUM(T.Amount) Total_Amount
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.cid=C2.cid AND C1.type=C2.type AND C1.RNum1=C2.RNum2   
                  JOIN #temp T ON C2.ledger_id=T.LedgerID
GROUP BY 
GROUPING SETS ((C1.cid,C1.type,C1.description,C1.ledgers,C2.ledger_id)
              ,(C1.cid,C1.type)
			  )

Output:

Best regards,

Cosmog Hong

---

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".