Question: PostgreSQL 9.4: Concatenate column values of two distinct rows into one single row with that column

Question

PostgreSQL 9.4: Concatenate column values of two distinct rows into one single row with that column

Answers 0
Added at 2016-12-31 03:12
Tags
Question

I have three tables:

1) Politics

CREATE TABLE politics (
"idPolitician" integer NOT NULL,
"publicName" character varying(150) NOT NULL,
"completeName" character varying(300) NOT NULL,
"publicBioLink" text,
"startDate" timestamp without time zone,
"endDate" timestamp without time zone,
draft boolean DEFAULT true,
CONSTRAINT politics_check CHECK (("startDate" < "endDate"))
);

2) Proposals

CREATE TABLE proposals (
"idProposal" integer NOT NULL,
description character varying(10000) NOT NULL,
"dateProposal" timestamp without time zone,
"linkProposal" character varying(1000),
"idCategory" integer,
"idProposalState" integer
);

3) PoliticsProposals (which is a many-to-many relation between the two tables mentioned above)

CREATE TABLE politicsproposals (
"idProposal" integer NOT NULL,
"idPolitician" integer NOT NULL
);

And I want to get all the proposals (description column only) associated to a specific politic (publicName and completeName) into a single row, delimiting the distinct proposals by a '|'. So, i want the row to look like this:

publicName    | completeName                      | proposals
Passos Coelho   Pedro Manuel Mamede Passos Coelho   proposaldescription1 | proposaldescription2

Here it the query i'm trying to do to accomplish that:

SELECT publicName, completeName, proposalDescription
FROM (SELECT "politics"."publicName" as publicName,
        "politics"."completeName" as completeName,
        proposals.description as proposalDescription,
        setweight(to_tsvector(unaccent("politics"."publicName")), 'B') ||
        setweight(to_tsvector(unaccent("politics"."completeName")), 'C') ||
        setweight(to_tsvector(unaccent(coalesce((string_agg(proposals.description, '|')), ''))), 'A') as document
  FROM politicsproposals
  JOIN politics ON "politics"."idPolitician" = "politicsproposals"."idPolitician"
  JOIN proposals ON "proposals"."idProposal" = "politicsproposals"."idProposal"
  GROUP BY "publicName", "completeName") politicsproposals_search
  WHERE politicsproposals_search.document @@ to_tsquery('coelho')

ORDER BY ts_rank(politicsproposals_search.document, to_tsquery('coelho'), 1)

And here it is the result of that query:

publicName          | completeName                      | proposals
Pedro Passos Coelho   Pedro Manuel Mamede Passos Coelho   Aumentar Os Impostos A Divida Da Segurança Social
Pedro Passos Coelho   Pedro Manuel Mamede Passos Coelho   Diminuir A Reforma Aos Senhores De Bengala

As you could notice, i got two duplicate rows respecting to the politic with the publicName "Pedro Passos Coelho".

Any help would be much grateful, and btw HAPPY NEW YEAR :)

Best regards.

Answers to

PostgreSQL 9.4: Concatenate column values of two distinct rows into one single row with that column

Source Show
◀ Wstecz