Dynamic column result data

Question

Hi,

First of all pls don't comment on table structure. its already created we cannot change anything.

I have 3 table like below. ##Invoice (which has the unique data) and ##ReInvoice (we will submit until its get fully paid. here we maintain level) and ##Payment (which has the payment history also maintain level)

create table ##Invoice (ID int, InvDate Date, Amount decimal(18,2))
insert into ##Invoice values (1001,'2022-01-01',1000);

create table ##ReInvoice (ID int, ReInvDate Date, Amount decimal(18,2), InvLevel int)
insert into ##ReInvoice values (1001,'2022-01-03',800,2)
insert into ##ReInvoice values (1001,'2022-01-05',500,3)

create table ##Payment (ID int, PayDate Date, Amount decimal(18,2), PayLevel int)
insert into ##Payment values (1001,'2022-01-02',200,1);
insert into ##Payment values (1001,'2022-01-04',300,2);
insert into ##Payment values (1001,'2022-01-06',400,3);

create table ##Result (ID int, InvDate Date, Amount decimal(18,2), PaidAmount decimal(18,2), Balance decimal(18,2), PaymentStatus nvarchar(20),
_1PaidDate Date, _1PaidAmount decimal(18,2),
_1ReInvDate Date, _1ReInvAmount decimal(18,2), _2PaidDate Date, _2PaidAmount decimal(18,2),
_2ReInvDate Date, _2ReInvAmount decimal(18,2), _3PaidDate Date, _3PaidAmount decimal(18,2))

insert into ##Result values (1001,'2022-01-01',1000,900,100,'Partial','2022-01-02',200,'2022-01-03',800,'2022-01-04',300,'2022-01-05',500,'2022-01-06',400);
--Status would be 'Open/Paid/Partial

select * from ##Invoice;
select * from ##ReInvoice;
select * from ##Payment;
select * from ##Result;

drop table ##Invoice
drop table ##ReInvoice
drop table ##Payment
drop table ##Result

I need resulted data like below ( the level is dynamic)

Thanks

Answer 1

Hi @kasim mohamed
Here is a query based on what i commented above. Please check:

DECLARE @Column_Names VARCHAR(MAX)  
DECLARE @SQL_String VARCHAR(MAX)  
  
SELECT @Column_Names = ISNULL(@Column_Names + ',','') + QUOTENAME(Column_Names)   
FROM (SELECT '_'+CAST(InvLevel AS VARCHAR(10))+Column_Name AS Column_Names   
      FROM ##ReInvoice CROSS APPLY(VALUES('ReInvDate'),('ReInvAmount'))C(Column_Name)  
      UNION  
      SELECT '_'+CAST(PayLevel AS VARCHAR(10))+Column_Name  
      FROM ##Payment CROSS APPLY(VALUES('PayDate'),('PayAmount'))C(Column_Name))S  
ORDER BY Column_Names  
--PRINT @Column_Names  
  
SET @SQL_String = '  
;WITH CTE1 AS  
(  
SELECT I.ID,I.InvDate,I.Amount,SUM(P.Amount) AS PaidAmount,I.Amount-SUM(P.Amount) AS Balance  
FROM ##Invoice I JOIN ##Payment P ON I.ID=P.ID  
GROUP BY I.ID,I.InvDate,I.Amount  
),CTE2 AS  
(  
 SELECT C.ID,C.InvDate,C.Amount,C.PaidAmount,C.Balance  
       ,R.ReInvDate AS [DATE],''_''+CAST(R.InvLevel AS VARCHAR(20))+''ReInvDate'' AS [DATE_TAG]  
	   ,R.Amount AS [Amount_Value],''_''+CAST(R.InvLevel AS VARCHAR(20))+''ReInvAmount'' AS [Amount_TAG]  
 FROM CTE1 C JOIN ##ReInvoice R ON C.ID=R.ID  
 UNION ALL  
 SELECT C.ID,C.InvDate,C.Amount,C.PaidAmount,C.Balance  
       ,P.PayDate AS [DATE],''_''+CAST(P.PayLevel AS VARCHAR(20))+''PayDate'' AS [DATE_TAG]  
	   ,P.Amount AS [Amount_Value],''_''+CAST(P.PayLevel AS VARCHAR(20))+''PayAmount'' AS [Amount_TAG]  
 FROM CTE1 C JOIN ##Payment P ON C.ID=P.ID  
),CTE3 AS   
(  
 SELECT ID,InvDate,Amount,PaidAmount,Balance,C.Column_Name,C.Column_Value  
       ,CASE WHEN Amount=PaidAmount THEN ''Paid''  
	         WHEN Amount>PaidAmount AND Balance > 0 THEN ''Partial''  
			 ELSE ''Open'' END AS PaymentStatus  
 FROM CTE2 CROSS APPLY(VALUES([DATE_TAG],CAST([DATE]AS VARCHAR(20))),([Amount_TAG],CAST([Amount_Value]AS VARCHAR(20))))C(Column_Name,Column_Value)  
)  
SELECT *  
FROM CTE3  
PIVOT(MAX(Column_Value)FOR Column_Name IN('+@Column_Names+'))P  
'  
--PRINT (@SQL_String)  
EXEC (@SQL_String)  

Output:

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.

Answer 2

create table ##Invoice (ID int, InvDate Date, Amount decimal(18,2))  
insert into ##Invoice values (1001,'2022-01-01',1000);  
  
create table ##ReInvoice (ID int, ReInvDate Date, Amount decimal(18,2), InvLevel int)  
insert into ##ReInvoice values (1001,'2022-01-03',800,2)  
insert into ##ReInvoice values (1001,'2022-01-05',500,3)  
  
create table ##Payment (ID int, PayDate Date, Amount decimal(18,2), PayLevel int)  
insert into ##Payment values (1001,'2022-01-02',200,1);  
insert into ##Payment values (1001,'2022-01-04',300,2);  
insert into ##Payment values (1001,'2022-01-06',400,3);  
  
create table ##Result (ID int, InvDate Date, Amount decimal(18,2), PaidAmount decimal(18,2), Balance decimal(18,2)  
, PaymentStatus nvarchar(20),  
_1PaidDate Date, _1PaidAmount decimal(18,2),  
_1ReInvDate Date, _1ReInvAmount decimal(18,2), _2PaidDate Date, _2PaidAmount decimal(18,2),  
_2ReInvDate Date, _2ReInvAmount decimal(18,2), _3PaidDate Date, _3PaidAmount decimal(18,2))  
  
insert into ##Result values (1001,'2022-01-01',1000,900,100,'Partial','2022-01-02',200,'2022-01-03',800,'2022-01-04',300,'2022-01-05',500,'2022-01-06',400);  
--Status would be 'Open/Paid/Partial  
  
--select * from ##Invoice;  
--select * from ##ReInvoice;  
--select * from ##Payment;  
select * from ##Result;  
  
;with mycte1 as (  
select ID  
,Max(case when InvLevel=2 then ReInvDate else null end) _1ReInvDate   
,Max(case when InvLevel=2 then Amount else null end) _1ReInvAmount  
,Max(case when InvLevel=3 then ReInvDate else null end) _2ReInvDate   
,Max(case when InvLevel=3 then Amount else null end) _2ReInvAmount  
  
from   ##ReInvoice  
Group by ID  
)  
, mycte2 as (  
select ID  
,Max(case when PayLevel=1 then PayDate else null end) _1PaidDate   
,Max(case when PayLevel=1 then Amount else null end) _1PaidAmount  
,Max(case when PayLevel=2 then PayDate else null end) _2PaidDate   
,Max(case when PayLevel=2 then Amount else null end) _2PaidAmount  
,Max(case when PayLevel=3 then PayDate else null end) _3PaidDate   
,Max(case when PayLevel=3 then Amount else null end) _3PaidAmount  
,SUM(Amount)  PaidAmount  
from   ##Payment  
Group by ID  
)  
  
select i.ID,i.InvDate,i.Amount,m2.PaidAmount,  
i.Amount-m2.PaidAmount  as Balance   
,Case when i.Amount>m2.PaidAmount then 'Partial'  
When i.Amount=m2.PaidAmount then 'Close'  
Else 'Open'  
End PaymentStatus,  
_1PaidDate, _1PaidAmount,  
_1ReInvDate,_1ReInvAmount,  
_2PaidDate,_2PaidAmount,  
_2ReInvDate,_2ReInvAmount,  
_3PaidDate,_3PaidAmount   
from ##Invoice i LEFT JOIN mycte1 m1 on i.ID=m1.ID  
LEFT JOIN mycte2 m2 on i.ID=m2.ID;  
   
  
drop table ##Invoice  
drop table ##ReInvoice  
drop table ##Payment  
drop table ##Result