Postgresql: How To Combine Multiple Rows?

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

Solution 1:

I suggest the following approach:

SELECT client_id, array_agg(result) AS results
    FROM labresults
    GROUPBY client_id;

It's not exactly the same output format, but it will give you the same information much faster and cleaner.

If you want the results in separate columns, you can always do this:

SELECT client_id,
       results[1] AS result1,
       results[2] AS result2,
       results[3] AS result3
FROM
(
    SELECT client_id, array_agg(result) AS results
        FROM labresults
        GROUPBY client_id 
) AS r
ORDERBY client_id;

although that will obviously introduce a hardcoded number of possible results.

Solution 2:

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

SELECT client_id,  
       MAX( CASE seq WHEN1THENresultELSE''END ) AS result1,  
       MAX( CASE seq WHEN2THENresultELSE''END ) AS result2,  
       MAX( CASE seq WHEN3THENresultELSE''END ) AS result3,  
       MAX( CASE seq WHEN4THENresultELSE''END ) AS result4,  
       MAX( CASE seq WHEN5THENresultELSE''END ) AS result5  
FROM ( SELECT p1.client_id, 
              p1.result,  
              ( SELECTCOUNT(*)  
                FROM labresults p2  
                WHERE p2.client_id = p1.client_id  
                AND p2.result <= p1.result )  
       FROM labresults p1 
) D ( client_id, result, seq )  
GROUPBY client_id;  

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