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?

Peter Eisentraut · Accepted Answer · 2011-08-26 10:15:24Z

22

I suggest the following approach:

SELECT client_id, array_agg(result) AS results
    FROM labresults
    GROUP BY 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
        GROUP BY client_id 
) AS r
ORDER BY client_id;

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

edited Aug 26, 2011 at 10:15

answered Jul 2, 2011 at 16:29

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SabreWolfy Over a year ago

Your solution works but I think the OP wants the data tabulated as in the question, so that it is easy to see where empty entries are (client 1 has no result E for example).

SabreWolfy Over a year ago

This answer solves the problem and should be accepted by the OP.

Wildcard Over a year ago

Brilliant, obvious in hindsight; I've been puzzling over how to do a simple (static names) "crosstab" for a database where I cannot create extension tablefunc, and array_agg() with the specific element selection as shown fits the bill perfectly.

thinzar00 · Accepted Answer · 2011-06-30 10:16:18Z

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..

edited Jun 30, 2011 at 10:16

user330315

answered Sep 30, 2010 at 4:36

thinzar00

5882 gold badges5 silver badges13 bronze badges

3 Comments

Frank Heikens Over a year ago

Use EXPLAIN ANALAYZE to see how the query is executed and what indexes are used. client_id does need an index.

thinzar00 Over a year ago

Thanks Frank.. I indexed the "client_id" and now it runs in less than 5000ms.

user330315 Over a year ago

With Postgres you don't need to "simulate" the row_number. That function is available since 8.4 (and if you are using an earlier version I would strongly recommend to upgrade as soon as possible)

Collectives™ on Stack Overflow

PostgreSQL: how to combine multiple rows?

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related