Avatar of Mohammad Alsolaiman
Mohammad Alsolaiman
Flag for Saudi Arabia

asked on 

SQL Some row data in table A, to be fixed order columns in table B

I have payment table, contains these fields:
  • Id
  • BscNo
  • PaymentDate
  • FinCode
  • CodeType
  • Amount
Every employee, in additional to his basic salary, he has several allowances & several deductions, stored in paymentTable.
The attached excel sheet has two sheets. One contains paymentTable with a sample data of 4 employee's records (allowances & deduction) for each.
  The other sheet has the intended table I need to have.
I need to have the full data for each employee in one row.
For instance, the first employee has 9 allowances, and 6 deductions which is represented in paymentTable in 15 rows. I need to have all of these rows to be in one row, and each amount should be placed under its allow/deduction code (which is became the column name in the other table(extractedTable) see the second excel sheet.
The other thing is that I need to have these columns fixed. I mean for instance the allowance code 102 should be always column number 4 in the extractedTable, column number 5 in the extractedTable should have the name 105 always, and so on. Because I'm running this process monthly, and this a customer requirement. Even if the whole column is null, keep it in its same order in the table.
help me, please.

question 01.xlsx

Avatar of undefined
Last Comment

8/22/2022 - Mon