Each rows to column values

Question

I'm trying to create a view that shows first table's columns plus second table's first 3 records sorted by date in 1 row.

I tried to select specific rows using offset from sub table and join to main table, but when joining query result is ordered by date, without

WHERE tblMain_id = ..

clause in joining SQL it returns wrong record.

Here is sqlfiddle example: sqlfiddle demo

tblMain

| id | fname | lname | salary |
+----+-------+-------+--------+
|  1 |  John |   Doe |   1000 |
|  2 |   Bob |  Ross |   5000 |
|  3 |  Carl | Sagan |   2000 |
|  4 | Daryl | Dixon |   3000 |

tblSub

| id |           email |  emaildate | tblmain_id |
+----+-----------------+------------+------------+
|  1 |   [email protected] | 2019-01-01 |          1 |
|  2 |   [email protected] | 2019-01-02 |          1 |
|  3 |   [email protected] | 2019-01-03 |          1 |
|  4 |   [email protected] | 2019-02-01 |          2 |
|  5 |   [email protected] | 2018-12-01 |          2 |
|  6 |  [email protected] | 2019-10-01 |          3 |
|  7 | [email protected] | 2019-11-01 |          4 |

View I am trying to achieve:

| id | fname | lname | salary |       email_1 | emaildate_1 |       email_2 | emaildate_2 |       email_3 | emaildate_3 |
+----+-------+-------+--------+---------------+-------------+---------------+-------------+---------------+-------------+
|  1 |  John |   Doe |   1000 | [email protected] |  2019-01-01 | [email protected] |  2019-01-02 | [email protected] |  2019-01-03 |

View I have created

| id | fname | lname | salary | email_1 | emaildate_1 |       email_2 | emaildate_2 |       email_3 | emaildate_3 |
+----+-------+-------+--------+---------+-------------+---------------+-------------+---------------+-------------+
|  1 |  John |   Doe |   1000 |  (null) |      (null) | [email protected] |  2019-01-01 | [email protected] |  2019-01-02 |

Answer 1

You can use conditional aggregation:

select m.id, m.fname, m.lname, m.salary,
       max(s.email) filter (where seqnum = 1) as email_1,
       max(s.emailDate) filter (where seqnum = 1) as emailDate_1,
       max(s.email) filter (where seqnum = 2) as email_2,
       max(s.emailDate) filter (where seqnum = 3) as emailDate_2,
       max(s.email) filter (where seqnum = 3) as email_3,
       max(s.emailDate) filter (where seqnum = 3) as emailDate_3
from tblMain m left join
     (select s.*,
             row_number() over (partition by tblMain_id order by emailDate desc) as seqnum
      from tblsub s
     ) s
     on s.tblMain_id = m.id           
where m.id = 1
group by m.id, m.fname, m.lname, m.salary;

Here is a SQL Fiddle.