SQL Some row data in table A, to be fixed order columns in table B
Hello,
I have payment table, contains these fields:
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
I have payment table, contains these fields:
- Id
- BscNo
- PaymentDate
- FinCode
- CodeType
- Amount
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm pleased the older, more generic approach ("conditional aggregates") solved your question, but here is another approach , T-SQL documentation for PIVOT:
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
Personally I still like conditional aggregates, but there is no good reason not to use pivot once you get familiar with it.
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot
Personally I still like conditional aggregates, but there is no good reason not to use pivot once you get familiar with it.
ASKER