Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl (care_team text, city text);
INSERT INTO tbl VALUES
  ('Team1', 'Franklin')
, ('Team1', 'Hopedale')
, ('Team1', 'Medway')
, ('Team1', 'Milford')
, ('Team2', 'city 1')
, ('Team2', 'city 4')
, ('Team2', 'city 3')
, ('Team2', 'city 2')
;

CREATE TABLE



INSERT 0 8


-- simple 1-parameter form of crosstab()
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);


Team
1
2
3
4


Team1
Franklin
Hopedale
Medway
Milford

Team2
city 1
city 2
city 3
city 4



SELECT 2


-- Conditional ordered-set agg func
SELECT care_team AS "Team"
     , percentile_disc (0)    WITHIN GROUP (ORDER BY city) AS "1"
     , percentile_disc (.251) WITHIN GROUP (ORDER BY city) AS "2"
     , percentile_disc (.501) WITHIN GROUP (ORDER BY city) AS "3"
     , percentile_disc (.751) WITHIN GROUP (ORDER BY city) AS "4"
FROM   tbl
GROUP  BY 1;


Team
1
2
3
4


Team1
Franklin
Hopedale
Medway
Milford

Team2
city 1
city 2
city 3
city 4



SELECT 2


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


Team
cities


Team1
Franklin, Hopedale, Medway, Milford

Team2
city 1, city 4, city 3, city 2



SELECT 2


-- Your hidden agenda with sorted cities
SELECT care_team AS "Team", string_agg(city, ', ' ORDER BY city) AS cities
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

-- the same, faster:
SELECT care_team AS "Team", string_agg(city, ', ') AS cities
FROM   (SELECT care_team, city FROM tbl ORDER BY 1, 2) sub
GROUP  BY 1;


Team
cities


Team1
Franklin, Hopedale, Medway, Milford

Team2
city 1, city 2, city 3, city 4



SELECT 2




Team
cities


Team1
Franklin, Hopedale, Medway, Milford

Team2
city 1, city 2, city 3, city 4



SELECT 2

Team	1	2	3	4
Team1	Franklin	Hopedale	Medway	Milford
Team2	city 1	city 2	city 3	city 4

Team	cities
Team1	Franklin, Hopedale, Medway, Milford
Team2	city 1, city 4, city 3, city 2