pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to display multiple rows in 1 row
6+ messages / 3 participants
[nested] [flat]
* How to display multiple rows in 1 row
@ 2017-01-10 19:55 Baxter Allen <baxter.allen@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Baxter Allen @ 2017-01-10 19:55 UTC (permalink / raw)
To: 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:
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
table_b
_id valA valB
1.table_b 1 2
2.table_b 4 7
3.table_b 5 4
table_c
_id valC valD
1.table_c 4 3
2.table_c 3 4
3.table_c 2 21
Using the following code:
Select distinct C.individual, A.*, B.*
FROM table_a A
LEFT JOIN table_b B ON A._id = B._id
LEFT JOIN table_c C ON A._id = C._id
order by A.individual
I get the following output:
individual _id valA valB _id valC valD
1 1.table_b 1 2
1 1.table_c 4 3
2 2.table_b 4 7
2 2.table_c 3 4
3 3.table_b 5 4
3 3.table_c 2 21
Instead, I would like the output for each individual to be all on a single
row:
individual _id valA valB _id valC valD
1 1.table_b 1 2 1.table_c 4 3
2 2.table_b 4 7 2.table_c 3 4
3 3.table_b 5 4 3.table_c 2 21
Any idea how I could make that happen?
Thanks!
Baxter
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to display multiple rows in 1 row
@ 2017-01-10 20:24 David G. Johnston <david.g.johnston@gmail.com>
parent: Baxter Allen <baxter.allen@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2017-01-10 20:24 UTC (permalink / raw)
To: Baxter Allen <baxter.allen@gmail.com>; +Cc: 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
> _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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to display multiple rows in 1 row
@ 2017-01-11 03:11 Steve Midgley <science@misuse.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Midgley @ 2017-01-11 03:11 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Baxter Allen <baxter.allen@gmail.com>; pgsql-sql
On Tue, Jan 10, 2017 at 12:24 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> 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
>> _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.
>
> Hi,
Another approach: I think the problem is that you're joining table_b and
table_c with the same join statement, which isn't quite right. Doing it
that way causes table_a to match either table_b or table_c, but never both
- so you get the "interleaved" effect on your data that you are trying to
avoid. By joining table_a and table_b first, you create a single "virtual"
table, then joining that to table_c causes c to be appended to the end of
this whole table.
Given the DDL at the bottom of my email (what I used to test), this sql
statement works, I think:
select * from
(select a.id as a_id, * from table_a a
left join table_b b on a.id=b.id) a1
join table_c c on a1.a_id = c.id
Returns
"1.table_c";"1.table_c";1;"";;;"1.table_c";4;3
"2.table_c";"2.table_c";2;"";;;"2.table_c";3;4
"3.table_c";"3.table_c";3;"";;;"3.table_c";2;21
I hope that helps?
Steve
p.s. Minor point, but if you provide some DDL for your tables and data on
future questions it makes it easier to come up with a solution.
--
-- TOC entry 185 (class 1259 OID 16411)
-- Name: table_a; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_a (
id text NOT NULL,
individual integer
);
ALTER TABLE table_a OWNER TO postgres;
--
-- TOC entry 186 (class 1259 OID 16419)
-- Name: table_b; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_b (
id text NOT NULL,
vala integer,
valb integer
);
ALTER TABLE table_b OWNER TO postgres;
--
-- TOC entry 187 (class 1259 OID 16440)
-- Name: table_c; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE table_c (
id text NOT NULL,
valc integer,
vald integer
);
ALTER TABLE table_c OWNER TO postgres;
--
-- TOC entry 2133 (class 0 OID 16411)
-- Dependencies: 185
-- Data for Name: table_a; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_a (id, individual) FROM stdin;
1.table_b 1
2.table_b 2
3.table_b 3
1.table_c 1
2.table_c 2
3.table_c 3
\.
--
-- TOC entry 2134 (class 0 OID 16419)
-- Dependencies: 186
-- Data for Name: table_b; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_b (id, vala, valb) FROM stdin;
1.table_b 1 2
2.table_b 4 7
3.table_b 5 4
\.
--
-- TOC entry 2135 (class 0 OID 16440)
-- Dependencies: 187
-- Data for Name: table_c; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY table_c (id, valc, vald) FROM stdin;
1.table_c 4 3
2.table_c 3 4
3.table_c 2 21
\.
--
-- TOC entry 2011 (class 2606 OID 16418)
-- Name: table_a table_a_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_a
ADD CONSTRAINT table_a_pkey PRIMARY KEY (id);
--
-- TOC entry 2013 (class 2606 OID 16426)
-- Name: table_b table_b_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_b
ADD CONSTRAINT table_b_pkey PRIMARY KEY (id);
--
-- TOC entry 2015 (class 2606 OID 16447)
-- Name: table_c table_c_pkey; Type: CONSTRAINT; Schema: public; Owner:
postgres
--
ALTER TABLE ONLY table_c
ADD CONSTRAINT table_c_pkey PRIMARY KEY (id);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to display multiple rows in 1 row
@ 2017-01-11 03:40 David G. Johnston <david.g.johnston@gmail.com>
parent: Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2017-01-11 03:40 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: Baxter Allen <baxter.allen@gmail.com>; pgsql-sql
On Tuesday, January 10, 2017, Steve Midgley <science@misuse.org> wrote:
>
> Given the DDL at the bottom of my email (what I used to test), this sql
> statement works, I think:
>
> select * from
> (select a.id as a_id, * from table_a a
> left join table_b b on a.id=b.id) a1
> join table_c c on a1.a_id = c.id
>
>
> Returns
>
> "1.table_c";"1.table_c";1;"";;;"1.table_c";4;3
> "2.table_c";"2.table_c";2;"";;;"2.table_c";3;4
> "3.table_c";"3.table_c";3;"";;;"3.table_c";2;21
>
>
Aside from executing without error I don't see how this works at all.
You cannot solve the posed problem by rearranging simple joins.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to display multiple rows in 1 row
@ 2017-01-11 03:55 David G. Johnston <david.g.johnston@gmail.com>
parent: Baxter Allen <baxter.allen@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2017-01-11 03:55 UTC (permalink / raw)
To: Baxter Allen <baxter.allen@gmail.com>; +Cc: pgsql-sql
On Tuesday, January 10, 2017, Baxter Allen <baxter.allen@gmail.com> wrote:
>
> Instead, I would like the output for each individual to be all on a single
> row:
> individual _id valA valB _id valC valD
> 1 1.table_b 1 2 1.table_c 4 3
> 2 2.table_b 4 7 2.table_c 3 4
> 3 3.table_b 5 4 3.table_c 2 21
>
>
The other approach is to "GROUP BY individual" and wrap every other column
in an aggregate function that retains the first non-null value in the group
(of which there should only be one). I don't recall there being a built-in
to do this but I'm certain it can be manually added and examples found on
the internet.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to display multiple rows in 1 row
@ 2017-01-11 04:01 Steve Midgley <science@misuse.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Steve Midgley @ 2017-01-11 04:01 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Baxter Allen <baxter.allen@gmail.com>; pgsql-sql
On Tue, Jan 10, 2017 at 7:40 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Tuesday, January 10, 2017, Steve Midgley <science@misuse.org> wrote:
>
>>
>> Given the DDL at the bottom of my email (what I used to test), this sql
>> statement works, I think:
>>
>> select * from
>> (select a.id as a_id, * from table_a a
>> left join table_b b on a.id=b.id) a1
>> join table_c c on a1.a_id = c.id
>>
>>
>> Returns
>>
>> "1.table_c";"1.table_c";1;"";;;"1.table_c";4;3
>> "2.table_c";"2.table_c";2;"";;;"2.table_c";3;4
>> "3.table_c";"3.table_c";3;"";;;"3.table_c";2;21
>>
>>
> Aside from executing without error I don't see how this works at all.
> You cannot solve the posed problem by rearranging simple joins.
>
> David J.
>
Ugh - my bad - I only checked the table_c data in my results and got what I
was expecting. I'm out of time tonight to play with this - apologies for
bogus response. I'll see if I can rectify tomorrow.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-01-11 04:01 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-10 19:55 How to display multiple rows in 1 row Baxter Allen <baxter.allen@gmail.com>
2017-01-10 20:24 ` David G. Johnston <david.g.johnston@gmail.com>
2017-01-11 03:11 ` Steve Midgley <science@misuse.org>
2017-01-11 03:40 ` David G. Johnston <david.g.johnston@gmail.com>
2017-01-11 04:01 ` Steve Midgley <science@misuse.org>
2017-01-11 03:55 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox