Thread: [SQL] How to display multiple rows in 1 row
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
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.
On Tue, Jan 10, 2017 at 12:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Hi,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_aLEFT JOIN table_b ON (b_id = table_b._id)LEFT JOIN table_c ON (c_id = table_c._id)David J.
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:
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);
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: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.
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.
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: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.