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 |   John@Doe1.com | 2019-01-01 |          1 |
|  2 |   John@Doe2.com | 2019-01-02 |          1 |
|  3 |   John@Doe3.com | 2019-01-03 |          1 |
|  4 |   Bob@Ross1.com | 2019-02-01 |          2 |
|  5 |   Bob@Ross2.com | 2018-12-01 |          2 |
|  6 |  Carl@Sagan.com | 2019-10-01 |          3 |
|  7 | Daryl@Dixon.com | 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 | John@Doe1.com |  2019-01-01 | John@Doe2.com |  2019-01-02 | John@Doe3.com |  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) | John@Doe1.com |  2019-01-01 | John@Doe2.com |  2019-01-02 |

score 2 · Accepted Answer · answered Sep 20 '19 at 00:17

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.

GMB · Answer 2 · 2019-09-20T00:33:02.953

Here is a solution that should get you what you expect.

This works by first ranking records within each table and joining them together. Then, the outer query uses aggregation to generate the expected output.

This solution will work even if the first record in the main table does not have id 1. Also filtering takes occurs within the JOINs, so this should be quite efficient.

SELECT
    m.id,
    m.fname,
    m.lname,
    m.salary,
    MAX(CASE WHEN s.rn = 1 THEN s.email END) email_1,
    MAX(CASE WHEN s.rn = 1 THEN s.emaildate  END) email_date1,
    MAX(CASE WHEN s.rn = 2 THEN s.email END) email_2,
    MAX(CASE WHEN s.rn = 2 THEN s.emaildate  END) email_date2,
    MAX(CASE WHEN s.rn = 3 THEN s.email END) email_3,
    MAX(CASE WHEN s.rn = 3 THEN s.emaildate  END) email_date3
FROM 
    (
        SELECT m.*, ROW_NUMBER() OVER(ORDER BY id) rn
        FROM tblMain
    ) m
    INNER JOIN (
        SELECT 
            email, 
            emaildate, 
            ROW_NUMBER() OVER(PARTITION BY id ORDER BY emaildate) rn
        FROM tblSub

    ) s 
    ON m.id = s.tblmain_id 
    AND m.rn = 1 
    AND s.rn <= 3
GROUP BY 
    m.id,
    m.fname,
    m.lname,
    m.salary

Each rows to column values

2 Answers2