Suppose 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 a conclusion

 role_name user_name --------- ----------- admin Ivanov II admin Petrov PP user Sidorov SS user Ivanov PS

Is it possible to make, when changing role_name empty string is added (or any given one), so that the output becomes

 role_name user_name --------- ----------- admin Ivanov II admin Petrov PP <-- тут либо пустая строка, либо разделитель user Sidorov SS user Ivanov PS
  • 2
    And you don’t want to do this on the client, the database is poorly adapted for tasks of beautiful output design ... Although of course from the SQL point of view, the task is interesting ... - Mike
  • On the client, I have only thoughts to compare the previous value of the field with the current one, and add a blank line when changing, which is quite laborious for the client, and not very nice. That's interesting, maybe you can on the server. In MSSQL this seems to be done with the help of rollup and the case when groupping(role_name)=1 then "" else role_name end (I can deceive with the code, I have not worked with MSSQL long time) - Edward Izmalkov
  • That's what they do, compare the previous with the following. And the sqlite dialect is rather weak, rollup is hard to emulate. Yes, in addition, it is necessary to properly sort after him ... - Mike
  • Sorted by field role_name initially, forgot to specify it, corrected the question. - Edward Izmalkov

1 answer 1

 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
  • Thanks, it works, but I didn’t quite understand the sorting by user_name . Implemented as case when user_name is null then 'яяяяяяяяяяя' else user_name end , so that the empty line was under the group - Edward Izmalkov
  • @ Edward Iszalkov So there was nothing in the question about sorting by user, so I just did 0 and 1 to make NULL at the end - Mike