Postgres Pro
Downloads
Home   >   mailing lists

Re: [SQL] How to display multiple rows in 1 row - Mailing list pgsql-sql

From David G. Johnston
Subject Re: [SQL] How to display multiple rows in 1 row
Date
Msg-id CAKFQuwZZnTXhqpLc8LtJ90t=sCOyK0FxPRJg9wWkvR_dy1HX4w@mail.gmail.com
Whole thread Raw
In response to [SQL] How to display multiple rows in 1 row  (Baxter Allen <baxter.allen@gmail.com>)
Responses Re: [SQL] How to display multiple rows in 1 row
List pgsql-sql
Tree view
On Tue, Jan 10, 2017 at 12:55 PM, Baxter Allen <baxter.allen@gmail.com> wrote:
Hello,

I have a database with a large number of individuals, and for each of these individuals there are entries in multiple tables as follows:

​If you can add "individual" as a FK on tables B and C - and make it a PK on table A - your life would become a whole lot easier.
 

table_a
_idindividual
1.table_b1
2.table_b2
3.table_b3
1.table_c1
2.table_c2
3.table_c3

​​
De-normalize table_a to match your desired output:

WITH recast_table_a AS (
​SELECT inds.individual,
(SELECT _id FROM table_a WHERE table_a.individual = inds.individual AND _id ~ 'table_b') AS b_id​,
(SELECT _id FROM table_a WHERE table_a.individual = inds.individual AND _id ~ 'table_c') AS c_id​,
FROM (​SELECT DISTINCT individual​ FROM table_a) inds
​)

Then join in the other tables:
SELECT *
FROM recast_table_a
LEFT JOIN table_b ON (b_id = ​table_b._id)
LEFT JOIN table_c ON (c_id = table_c._id)

David J.

pgsql-sql by date:

Previous
From: Baxter Allen
Date:
Subject: [SQL] How to display multiple rows in 1 row
Next
From: Steve Midgley
Date:
Subject: Re: [SQL] How to display multiple rows in 1 row