Help Breaking down table with 40 columns to multiple records

Question

Hi Guys,

I am trying to break a table that contains records accross in 40 columns and create multiple records out of the 40 columns record.

I have 2 Tables, TableA the one that has 40 Columns per Enrollment(Matricula) and the other TableB the one that only has 5 Columns which will store the multiple records broken down from the TableA

TableA = tblPayments

TableB = Payment_Details

Some how the records do not fall in the appropriate columns and this is the part where I am frustrated

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*  
  uspCalculateTimeSheet  
  Syntax: spBreakDownPayments 
*/  
  
CREATE PROC [dbo].[spBreakDownPayments]  

AS  
 
DECLARE @Matricula varchar(50)
DECLARE @LineCnt int
DECLARE @sqlMethod NVARCHAR(4000)
DECLARE @OPMethod NVARCHAR(50)
DECLARE @sqlDate NVARCHAR(4000)
DECLARE @OPDate NVARCHAR(50)
DECLARE @sqlAmt NVARCHAR(4000)
DECLARE @OPAmt NVARCHAR(50)
DECLARE @TheMethod varchar(50) 
DECLARE @TheDate datetime
DECLARE @TheAmount money         

SET @LineCnt=0  
SET @TheAmount=0

DECLARE Cursor_Payments CURSOR FOR  
        SELECT DISTINCT [Matricula] FROM [DBF_File].[dbo].[tblPayments] WHERE [Matricula] IS NOT NULL

OPEN Cursor_Payments  
FETCH NEXT FROM Cursor_Payments INTO @Matricula
WHILE @@FETCH_STATUS=0  
BEGIN  
		SET @LineCnt = @LineCnt +1		

		   
			SET @sqlMethod = 'SELECT @TheMethod=method'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula' 
			EXEC sp_executesql @sqlMethod, N'@Matricula INT, @TheMethod NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheMethod = @TheMethod OUTPUT
			---SELECT @TheMethod=ISNULL(@OPMethod,'CASH') 			

			SET @sqlDate ='SELECT @TheDate=paiddate'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula' 
			EXEC sp_executesql @sqlDate, N'@Matricula INT, @TheDate NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheDate = @TheDate OUTPUT
			---SELECT @TheDate=TRY_CONVERT(DATETIME,@OPDate) 
			

			SET @sqlAmt = 'SELECT @TheAmount=Amount'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula' 
			EXEC sp_executesql @sqlAmt, N'@Matricula INT, @TheAmount NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheAmount = @TheAmount OUTPUT
			---SELECT @TheAmount=ISNULL(TRY_CONVERT(MONEY,@OPAmt),0) 				
			
			
			------------Append new record  	    
			IF NOT EXISTS(select * from Payment_Details where [reference]=@Matricula and [method]=@TheMethod and MONTH([date_paid])=MONTH(@TheDate) and DAY([date_paid])=DAY(@TheDate) and YEAR([date_paid])=YEAR(@TheDate) and [amount_paid]=@TheAmount)
			   BEGIN
			        BEGIN TRANSACTION
						INSERT INTO [Payment_Details]([date_paid],[method],[amount_paid],[reference])
						VALUES(@TheDate,@TheMethod,@TheAmount,@Matricula)
					COMMIT	
                END				
			----Reset
			SET @TheMethod=''
			SET @TheDate=NULL
			SET @TheAmount=0.00
           	  		
		---- It Reaches the columns count then go to the next		
        IF (@LineCnt>=40)
            BEGIN		
				SET @LineCnt = 0 
            END
	   	
   -----Next Record
   FETCH NEXT FROM Cursor_Payments INTO @Matricula
END  
CLOSE Cursor_Payments
Deallocate Cursor_Payments

Download the data and tables script -->"github.com/netstair/SQLBreakdownData/blob/main/DBF_File_Script_With_Data.sql"

Thank you in advanced.

Jose