CODEWITHSUNDEEP
sqldatabasepostgresqltransposecrosstab
Tuguldur
Tuguldur

Reputation: 31

Each rows to column values

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 |

Upvotes: 2

Views: 83

Answers (2)

GMB
GMB

Reputation: 222702

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Related Questions