sql - combine two columns into one column rows but not same cell and categorize them

Question

I have two tables, "TableA" for levels (Admin, Moderator, Agent ... etc) and "TableB" for users with a column indicates the level ID referring to "TableA". I want a stored procedure to categorize usernames under the levels but the result must be in one column only like this:

Those are my two tables:

TableA
+---------------------------+
|Level ID     |Level Name   |
+---------------------------+
|1            |Admin        |
+---------------------------+
|2            |Moderator    |
+---------------------------+
|3            |Agent        |
+---------------------------+

TableB
+---------------------------+
|Username     |Level ID     |
+---------------------------+
|John         |1            |
+---------------------------+
|Sam          |2            |
+---------------------------+
|Tommy        |2            |
+---------------------------+
|Tony         |3            |
+---------------------------+
|Patrick      |3            |
+---------------------------+
|Jimmy        |3            |
+---------------------------+
|Tod          |3            |
+---------------------------+

This is how I want the result of the query:

+-------------+
|Admin        |
+-------------+
|  John       |
+-------------+
|             |
+-------------+
|Moderator    |
+-------------+
|  Sam        |
+-------------+
|  Tommy      |
+-------------+
|             |
+-------------+
|Agent        |
+-------------+
|  Tony       |
+-------------+
|  Patrick    |
+-------------+
|  Jimmy      |
+-------------+
|  Tod        |
+-------------+

It has to be one column only and the spaces before names can be added with

CONCAT(' ', TableA.Username)

There's an empty cell after last name in each level category. I'm using SQL management studio 18

标签： sqlsql-serverstored-procedures

Answer 1

Use union all and order by:

select name
from ((select levelname as name, levelid, 1 as ord
       from tablea
      ) union all
      (select '  ' + username, levelid, 2 as ord
       from tableb
      )
     ) ul
order by levelid, ord;

This doesn't actually include the blank rows, which you can also include:

select name
from ((select levelname as name, levelid, 1 as ord
       from tablea
      ) union all
      (select '  ' + username, levelid, 2 as ord
       from tableb
      ) union all
      (select null, levelid, 0 as ord
       from tablea
       where levelid > 1
      )
     ) ul
order by levelid, ord;

All that said. You can do this transformation in SQL. However, it is more typical to do such formatting in the application layer.