0

I have table Service and table Service_Payment, each service can be paid using 1 or more payment methods.

Table Service:

service_id `PK`,
service_name,
service_fees

Table Service_Payment:

service_payment_id `PK`,
service_id `FK`,
service_payment_method,
service_payment_amount

Example Data:

service_id: 1,
service_name: "Transportation",
service_fees: 1000

service_payment_id: 1,
service_id: 1,
service_payment_method: "Cash",
service_payment_amount: 300

service_payment_id: 2,
service_id: 1,
service_payment_method: "Credit Card",
service_payment_amount: 500

service_payment_id: 3,
service_id: 1,
service_payment_method: "Driver Collection",
service_payment_amount: 200

Now its showing that this service has 3 different payments (Cash, Credit Cart and Driver Collection).

I need to select all services with their corresponding payments in terms of payment methods in the same row.

For Example:

service_id: 1,
service_name: "Transportation",
service_fees: 1000,
cash_value: 300,
credit_card_value: 500,
driver_collection_value: 200,
bank_transfer_value: 0

I tried joining table Service_Payment but obviously I get duplicated rows.

How can I solve this?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Abdelrahman Wahdan
  • 2,056
  • 4
  • 36
  • 43
  • 1
    See [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jan 09 '21 at 13:30
  • If the want the output as in the question then you are going to have to union for as many payment types as there can be using dynamic sql , on the other hand if the output should be tabular then you have to use dynamic sql and conditional aggregation - I for one won't work on this until clear what your output should look like, – P.Salmon Jan 09 '21 at 13:39
  • @P.Salmon I am looking to have tabular output just like any select query output but with extra custom columns (1 for each payment method) – Abdelrahman Wahdan Jan 09 '21 at 13:41

0 Answers0