Re: [SQL] How to display multiple rows in 1 row
| От | David G. Johnston |
|---|---|
| Тема | Re: [SQL] How to display multiple rows in 1 row |
| Дата | |
| Msg-id | CAKFQuwZZnTXhqpLc8LtJ90t=sCOyK0FxPRJg9wWkvR_dy1HX4w@mail.gmail.com обсуждение исходный текст |
| Ответ на | [SQL] How to display multiple rows in 1 row (Baxter Allen <baxter.allen@gmail.com>) |
| Ответы |
Re: [SQL] How to display multiple rows in 1 row
|
| Список | pgsql-sql |
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 _id individual 1.table_b 1 2.table_b 2 3.table_b 3 1.table_c 1 2.table_c 2 3.table_c 3
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 по дате отправления: