sqlite adding blank lines between groups

Question:

Let's say there is a request

SELECT r.role_name, u.user_name 
FROM user u 
INNER JOIN roles r ON u.role_id = r.id
ORDER BY r.role_name

which gives the conclusion

role_name   user_name
---------   -----------
admin       Ivanov I.I.
admin       Petrov P.P.
user        Sidorov S.S.
user        Ivanov P.S.

Is it possible to do so that when role_name changes, role_name empty string (or some given one) is added so that the output becomes like this

role_name   user_name
---------   -----------
admin       Ivanov I.I.
admin       Petrov P.P.
                             <-- тут либо пустая строка, либо разделитель
user        Sidorov S.S.
user        Ivanov P.S.

Answer:

select case when user_name is null then NULL else role_name end as role_name,
       user_name
  from (
     SELECT r.role_name, u.user_name 
      FROM user u INNER JOIN roles r ON u.role_id = r.id
    union all
     SELECT distinct r.role_name, NULL
      FROM user u INNER JOIN roles r ON u.role_id = r.id
  ) A
  order by A.role_name, case when user_name is null then 1 else 0 end
Scroll to Top