How do I pivot columns in PostgreSQL?

Question

I have a table with two columns: care_team and city:

I am trying to pivot these columns so that there is 1 row, and four additional columns. It should look something like this:

This might be super simple but I am in need of help.

Answer 1

crosstab()

You can pivot with the crosstab() function provided by the additional module tablefunc.

There is fineprint to this, but your question is light on details. In particular, why are your cities ordered the way they are in the result. I assume sorted alphabetically. And I assume there are always 4 cities per team. Else you may need to do more.

SELECT *
FROM   crosstab(
   'SELECT care_team, 1, city
    FROM   tbl
    ORDER  BY care_team, city'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Team" text, "1" text, "2"  text, "3" text, "4" text);

fiddle

In-depth instructions here:

• PostgreSQL Crosstab Query
• Sum by month and put months as columns

Conditional aggregates

One alternative would be with conditional aggregates. That works with standard SQL features, but crosstab() is typically faster.

The specific difficulty of your rump case is that there is no "category" column. So we would have to create one in a subquery, for instance with row_number(). But, based on the same assumptions as above, we can make it work on a single query level with the ordered-set aggregate function percentile_disc():

SELECT care_team AS "Team"
     , percentile_disc (0) WITHIN GROUP (ORDER BY city)
     , percentile_disc (.251) WITHIN GROUP (ORDER BY city)
     , percentile_disc (.501) WITHIN GROUP (ORDER BY city)
     , percentile_disc (.751) WITHIN GROUP (ORDER BY city)
FROM   tbl
GROUP  BY 1;

fiddle

Assuming 4 cities per team, each next value kicks in after an increment of 1/4 ...

See:

• Conditional SQL count
• How to aggregate percentile_disc() function over date time
• Can someone give examples of use case of the ordered-set functions in PostgreSQL 9.4?

Your hidden agenda

You later commented:

... two columns, one column being the team and the other, a string value comprised of all the cities within that team.

That's actually a lot simpler:

SELECT care_team AS "Team", string_agg(city, ', ') AS cities
FROM   tbl
GROUP  BY 1;

fiddle

See:

• Concatenate multiple result rows of one column into one, group by another column