PostgreSQL: how to combine multiple rows?

Question

I have a table like this to save the results of a medical checkup and the date of the report sent and the result. Actually the date sent is based on the clinic_visit date. A client can have one or more reports (date may varies)

---------------------------------------
|  client_id  |  date_sent  | result |
---------------------------------------
| 1           |   2001      |    A   |
| 1           |   2002      |    B   |
| 2           |   2002      |    D   |
| 3           |   2001      |    A   |
| 3           |   2003      |    C   |
| 3           |   2005      |    E   |
| 4           |   2002      |    D   |
| 4           |   2004      |    E   |
| 5           |   2004      |    B   |
---------------------------------------

I want to extract the following report from the above data.

---------------------------------------------------
|  client_id  |  result1  |  result2  |   resut3  |
---------------------------------------------------
|      1      |    A      |    B      |           |
|      2      |    D      |           |           |
|      3      |    A      |    C      |     E     |
|      4      |    D      |    E      |           |
|      5      |    B      |           |           |
---------------------------------------------------

I'm working on Postgresql. the "crosstab" function won't work here because the "date_sent" is not consistent for each client.

Can anyone please give a rough idea how it should be queried?

Answer 1

While I was reading about "simulating row_number", I tried to figure out another way to do this.

SELECT client_id,
MAX( CASE seq WHEN 1 THEN result ELSE '' END ) AS result1,
MAX( CASE seq WHEN 2 THEN result ELSE '' END ) AS result2,
MAX( CASE seq WHEN 3 THEN result ELSE '' END ) AS result3,
MAX( CASE seq WHEN 4 THEN result ELSE '' END ) AS result4,
MAX( CASE seq WHEN 5 THEN result ELSE '' END ) AS result5
FROM ( SELECT p1.client_id, p1.result,
( SELECT COUNT(*)
FROM labresults p2
WHERE p2.client_id = p1.client_id
AND p2.result <= p1.result )
FROM labresults p1 ) D ( client_id, result, seq )
GROUP BY client_id;

but the query took 10 minutes (500,000 ms++). for 30,000 records. This is too long..