Sql join query with total row

Mohamed Farook 161 Reputation points
2024-06-24T07:11:57.31+00:00

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

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,263 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,258 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2024-06-24T07:59:11.7433333+00:00

    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:

    User's image

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

    0 comments No comments

0 additional answers

Sort by: Most helpful