0

I need to write an sql query to accomplish the following operation:

Client_ID Payment Date
1 10$ 01/01/2020
2 5$ 01/01/2020
2 12$ 02/01/2020
2 6$ 04/01/2020
3 11$ 02/01/2020
3 15$ 05/01/2020

The table must be formatted so each client_id appears only once, and each payment made by the client must have a separate column for 1st, 2nd, 3rd payment ect..

Client_ID Payment 1 Payment 2 Payment 3
1 10$ 0$ 0$
2 5$ 12$ 6$
3 11$ 15$ 0$

Please help.

Nikola Blajev
  • 3
  • 1
  • You're looking for something called a "Pivot/crosstab" which can be handled manually though case expressions. If it's a dynamic pivot meaning you have a dynamic number of possible payments per client then this gets into dynamic SQL and is likely better handled though the user interface. So do you always have just 3 payments or could it be 1, 3, 5 or any number? Example of Dynamic though not recommended in a DB as it uses dynamic SQL which is usually sub optimal https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 – xQbert Dec 16 '21 at 17:21
  • Additional examples using an extension: https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 and using case expression which isn't dynamic: https://stackoverflow.com/questions/2477231/correct-way-to-create-a-pivot-table-in-postgresql-using-case-when – xQbert Dec 16 '21 at 17:26
  • Hi, thanks for the answer. – Nikola Blajev Dec 16 '21 at 17:58
  • The number of payments can vary, I just used 3 for the example. My idea is to use the maximum number of payments done by a client in order to determine the number of columns to be added. – Nikola Blajev Dec 16 '21 at 18:00
  • Why do you want the database to do this? Handling a varying number of columns is more a problem for the front end; there being a fixed number of columns and variable number of rows is generally a fairly core principle of database use/SQL. Sure, you can get around it, but it's awkward to do and makes the results typically mroe difficult to work with – Caius Jard Dec 16 '21 at 18:09
  • @CaiusJard it is just a task I am trying to learn how to do. No particular reason. – Nikola Blajev Dec 16 '21 at 18:11
  • Oof.. If there's no particular reason for doing a dynamic crosstab I wouldn't bother. I've no qualms about teaching you a fixed one though – Caius Jard Dec 16 '21 at 18:13
  • Yes, I do not feel that applying this dynamic crosstab is necessary. A static one would do just fine as the number of payments per client is not expected to exceed 45. – Nikola Blajev Dec 16 '21 at 18:27

1 Answers1

0

While what you ask can be accomplished you will find it is not worth the effort as pivoting requires advanced knowledge of the results, well at least the exact size. Since you have varying number of payments you will need to dynamically create the query. Meaning you write a code which looks at the data then writes the query before executing it. That can get very complex very quickly and the result is a maintenance nightmare. Let me propose the following instead.

Your idea to maximum number of payments done by a client in order to determine the number of columns has merit, but let SQL do that work (which it does easily), then let your client's presentation manager handle the pivoting. The following builds one row per customer with an array of payments for each customer and adding columns for number of payments for that customer and the maximum number of payments for any customer. (See demo).

select client_id, payments_made
     , array_length(payments_made,1) num_payments
     , max(array_length(payments_made,1)) over() max_payments
  from (     
         select client_id, array_agg(payment order by paid_date)  payments_made
           from payments 
          group by client_id
       ) gs;
halfer
  • 19,824
  • 17
  • 99
  • 186
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • I guess, if you consider listing all 45 possibilities in the query simpler. IMHO it is not. Is there a **business rule** stating no be more that 45 payments per client. Otherwise you are just making a arbitrary decision not supported by requirements. How about the client that for reasons on there end makes 2 regular payments instead of just 1. Do you now face the possibility of 90 payments during a given period.? As my mentor told me many many years ago: *As a developer your job is the systematic control of miracles*. In other words properly handle those events that can(not) happen. – Belayer Dec 16 '21 at 18:48
  • Thanks for the help, your code with the arrays seems to have accomplished what I was trying to achieve in a much simpler way :). – Nikola Blajev Dec 16 '21 at 19:19
  • Glade to help. If the answer solved your issue please accept it. This helps future questioners with the same/similar issue and removes the question from the unanswered queue. Kindly do not leave successfully answered question in the unanswered queue. – Belayer Dec 16 '21 at 19:55