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  (Steve Midgley <science@misuse.org>)
Список pgsql-sql
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Baxter Allen
Дата:
Сообщение: [SQL] How to display multiple rows in 1 row
Следующее
От: Steve Midgley
Дата:
Сообщение: Re: [SQL] How to display multiple rows in 1 row