Postgresql – SELECT multiple values from one table having matching record in another table in one row

join;pivotpostgresql

I have two tables as follows: 

create table first (id int, name varchar(20));
create table second (id int, value char(1),fid int);

These tables have the following records: 

insert into first values (1,'Ahmad'),(2,'Sami'),(3,'Khan');
insert into second values
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

I want to select those records from table second which have matching records in table first in a single row.

The desired output would be: 

 id | name  | value | value | value
 ---+-------+-------+-------+-------
  1 | Ahmad |   a   |   b   |   d
  2 | Sami  |   c   |   e   |
  3 | Khan  |   f   |       |

The number of values for a single record of table first is not limited and there can be n number of values for a single record of table first.

n is unknown, but I need to repeat the column value for each value in table second which has matching record in table first. I need the whole table like above The desired result. I need only those rows referenced by table second.
The version of Postgres will be the latest one.

Best Answer

Actually, since

there can be n number of values for a single record of table first.

the number of columns to return is not exactly arbitrary. There is a maximum of columns, and this has a clean solution - unless you have more columns than Postgres allows for a table:

250 - 1600 depending on column types

BTW, typically, you would also have a third table listing all possible values of value, the whole of it implementing a classical many-to-many relationship.

You can use CASE statements, or more elegantly, the crosstab() function of the additional module tablefunc. If you are unfamiliar with it, read basic instructions here first:

Columns to the right of actual values are filled with NULL. Assuming a maximum of 5 possible values and building on this setup:

CREATE TABLE first  (id int, name text);
CREATE TABLE second (id int, value "char", fid int);  

INSERT INTO first  VALUES
  (1,'Ahmad')
, (2,'Sami')
, (3,'Khan')
, (4,'Nobody');  -- Added to demonstrate difference
INSERT INTO second VALUES
  (1,'a',1)
, (2,'b',1)
, (3,'c',2)
, (4,'d',1)
, (5,'e',2)
, (6,'f',3);

Either use crosstab(text) (1 parameter form) and join to table first another time:

SELECT id, f.name, value1, value2, value3, value4, value5
FROM   crosstab(
        'SELECT f.id, 1 AS dummy_category, s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
       ) ct (id int
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char")
JOIN first f USING (id);

Or use crosstab(text, text) (2 parameter form) and generate dummy categories for your values:

SELECT *
FROM   crosstab(
        'SELECT f.id, f.name
              , row_number() OVER (PARTITION BY f.id ORDER BY s.value) AS dummy_category
              , s.value
         FROM   first  f
         JOIN   second s ON s.fid = f.id
         ORDER  BY f.id, s.value'
      , ('SELECT generate_series(1,5)')
       ) ct (id int, name text
           , value1 "char", value2 "char", value3 "char", value4 "char", value5 "char");

Result is the same either way:

 id | name  | value1 | value2 | value3 | value4 | value5
----+-------+--------+--------+--------+--------+--------
  1 | Ahmad | a      | b      | d      |        |
  2 | Sami  | c      | e      |        |        |
  3 | Khan  | f      |        |        |        |

If you want to include all rows from table first, make it a LEFT [OUTER] JOIN:

         ...
         LEFT JOIN second s ON s.fid = f.id
         ...

Then we get one additional result row for the above example:

  4 | Nobody |        |        |        |        |
Related Question