Re: [SQL] How to display multiple rows in 1 row
| От | Steve Midgley |
|---|---|
| Тема | Re: [SQL] How to display multiple rows in 1 row |
| Дата | |
| Msg-id | CAJexoSL3vBFfSdyEoqBP2-JWwV+i5vH9F+Ek+=m-eOo_kom3Ug@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [SQL] How to display multiple rows in 1 row ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: [SQL] How to display multiple rows in 1 row
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| Список | pgsql-sql |
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);
В списке pgsql-sql по дате отправления: