SQL: select values from rows to custom columns

Question

I have a table for Groups, ex:

[id]        [name]
1           Group1
2           Group2
3           Group3

and another table for their shedule:

[id]    [group_id]  [date]
1       1           2018-03-03
2       1           2018-03-09
3       1           2018-03-06
4       2           2018-03-08

How to use PIVOT or any other way to show each group with 3 last dates:

[group_id]  [group_name]    [date_1]    [date_2]    [date_3]
1           Group1          2018-03-09  2018-03-06  2018-03-03
2           Group2          2018-03-08  NULL        NULL
3           Group3          NULL        NULL        NULL

Seriously consider handling issues of data display in application code. — Strawberry, Mar 09 '18 at 12:27
100% agreed with @Strawberry here... to do such a thing in such a way you gona need dynamic SQL and it gona be painful as H to maintain it. — Jorge Campos, Mar 09 '18 at 12:29

score 0 · Answer 1 · answered Mar 09 '18 at 12:25

If you can bear having the dates in one column concatenated together, then a shortcut is:

select g.id, g.name,
       substring_index(group_concat(date order by date desc), ',', 3)
from groups g left join
     schedule s
     on g.id = s.group_id
group by g.id, g.name;

Note: There is a maximum length for the intermediate value for group_concat(). It is 1024 bytes, which is fine for most purposes, but can easily be made larger.

Alex Zen · Accepted Answer · 2018-03-09T16:28:43.157

This should do it:

SELECT t.id AS group_id,
       t.name AS group_name,
       MAX(CASE t.row_num WHEN 1 THEN t.date ELSE NULL END) AS date_1,
       MAX(CASE t.row_num WHEN 2 THEN t.date ELSE NULL END) AS date_2,
       MAX(CASE t.row_num WHEN 3 THEN t.date ELSE NULL END) AS date_3
  FROM (SELECT t1.id,
               t1.name,
               t2.date,
               ROW_NUMBER() OVER(PARTITION BY t1.id ORDER BY t2.date DESC) row_num
          FROM TAB_1 t1
          LEFT OUTER JOIN TAB_2 t2
            ON t1.id = t2.group_id) t
 GROUP BY t.id, t.name

It uses ROW_NUMBER() to rank dates for each group, and then selects the correct one for each column.

First step: rank the dates for each group

[group_id]  [group_name]   [date]      [row_num]
1           Group 1        2018-03-09  1
1           Group 1        2018-03-06  2
1           Group 1        2018-03-03  3
2           Group 2        2018-03-08  1
3           Group 3        NULL        1

Second step: direct dates to the right column

[group_id]  [group_name]   [date_1]       [date_2]       [date_3]
1           Group 1        2018-03-09     NULL           NULL
1           Group 1        NULL           2018-03-06     NULL
1           Group 1        NULL           NULL           2018-03-03
2           Group 2        2018-03-08     NULL           NULL
3           Group 3        NULL           NULL           NULL

Third step: get the MAX() for each column

[group_id]  [group_name]   [date_1]       [date_2]       [date_3]
1           Group 1        2018-03-09     2018-03-06     2018-03-03
2           Group 2        2018-03-08     NULL           NULL
3           Group 3        NULL           NULL           NULL

It looks like MySQL doesn't have this analytic function though. I don't have much experience on MySQL, but maybe you could make it work anyway. Please check this out: ROW_NUMBER() in MySQL

SQL: select values from rows to custom columns

2 Answers2