1

I'm using postgres 9.3.5.

Given the following data:

select * from department;
 id |   name    
----+-----------
  1 | sales
  2 | marketing
  3 | HR

and

select * from people;
 id | department_id | first_name | last_name 
----+---------------+------------+-----------
  1 |             1 | Tom        | Jones
  2 |             1 | Bill       | Cosby
  3 |             2 | Jessica    | Biel
  4 |             1 | Rachel     | Hunter
  5 |             2 | John       | Barnes

I'd like to return a result set like this:

 id |   name    | first_name-1 | last_name-1 | first_name-2 | last_name-2 | first_name-3 | last_name-3
----+-----------+--------------+-------------+--------------+-------------+--------------+------------ 
  1 | sales     | Tom          | Jones       | Bill         | Cosby       | Rachel       | Hunter
  2 | marketing | Jessica      | Biel        | John         | Barnes
  3 | HR        |

Is this possible?

The answer provided here by Max Shawabkeh using the GROUP_CONCAT is close - but its not returning as extra fields in the dataset, its concatenating them into a single field.

Community
  • 1
  • 1
CodeBuddy
  • 5,749
  • 1
  • 25
  • 30

1 Answers1

1

You need cross-tabulation (sometimes called pivot).

Could look like this in your case:

SELECT * FROM crosstab(
       $$SELECT d.id, d.name,'p' AS dummy_cat 
               ,concat_ws(' ', p.first_name, p.last_name) AS person
         FROM   department  d
         LEFT   JOIN people p ON p.department_id = d.id
         ORDER  BY d.department_id, p.id$$
       )
AS ct (id int, department text, person_1 text, person_2 text, person_3 text);

Returns:

id  department  person_1      person_2     person_3
--------------------------------------------------------
1   sales       Tom Jones     Bill Cosby   Rachel Hunter
2   marketing   Jessica Biel  John Barnes  <NULL>
3   HR          <NULL>        <NULL>       <NULL>

Very similar to this related case (explanation for special difficulties there):

But this case is simpler: since you do not seem to care about the order in which persons are listed, you can use the basic one-parameter form of crosstab().

Also, according to your comment, you want all departments, even if no people are assigned. Adjusted the LEFT JOIN accordingly.

Basic details in this related answer:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin, very close to the output I'm looking for - missing the NULL row for HR and separate columns for first_name and last_name. – CodeBuddy Aug 14 '14 at 07:31
  • @CodeBuddy: I inverted the `LEFT JOIN` to include empty departments and simplified the query. Separate names, can be solved, too. Decompose in an outer query. – Erwin Brandstetter Aug 14 '14 at 07:53
  • Thanks again, but new query returns an error for me: ERROR: column d.department_id does not exist – CodeBuddy Aug 14 '14 at 08:37
  • @CodeBuddy: Obviously, that's `d.id` (I never use non-descriptive words like "id" or "name" as identifiers in my own designs.) – Erwin Brandstetter Aug 14 '14 at 14:24