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

Answer 1

The best way would be get the records from database and do whatever you want on code side(if you have one).

But if you want it in stored procedure and records in 'TableA' changes frequently(I don't think so) then you need to use loop. I wouldn't recommend this, because loops in queries affects query execution time.

That's why I would share a solution with hardcoded 'LevelNames' in query:

CREATE PROCEDURE sp_GetDataInOneColumn

AS
BEGIN
    SELECT 'Admin'
    UNION ALL
    SELECT CONCAT(' ', Username) FROM TableB WHERE [Level ID] = 1
    UNION ALL
    SELECT ''
    UNION ALL
    SELECT 'Moderator'
    UNION ALL
    SELECT CONCAT(' ', Username) FROM TableB WHERE [Level ID] = 2
    UNION ALL
    SELECT ''
    UNION ALL
    SELECT 'Agent'
    UNION ALL
    SELECT CONCAT(' ', Username) FROM TableB WHERE [Level ID] = 3
END

Here is sql stored procedure with loop:

CREATE PROCEDURE sp_GetDataInOneColumnWithLoop

AS
BEGIN

    CREATE TABLE #DataInOneColumn (
        Names VARCHAR(MAX)
    );

    SELECT *
    INTO   #Temp
    FROM   TableA

    DECLARE @LevelId int
    DECLARE @LevelName nvarchar(100)    

    WHILE EXISTS(SELECT * FROM #Temp)
    BEGIN

        SELECT TOP 1 @LevelId = [Level ID], @LevelName = [Level Name] From #Temp ORDER BY [Level ID]

        IF (EXISTS(SELECT * FROM TableB WHERE [Level ID] = @LevelId))
        BEGIN       
            INSERT INTO #DataInOneColumn VALUES('')
            INSERT INTO #DataInOneColumn VALUES(@LevelName)
            INSERT INTO #DataInOneColumn SELECT CONCAT(' ', Username) FROM TableB WHERE [Level ID] = @LevelId
        END
        DELETE #Temp WHERE [Level ID] = @LevelId

    END

    SELECT Names FROM #DataInOneColumn
END

Answer 2

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.

Answer 3

This "smells" of putting "view/output" logic into the database.. if you've got the levels and usernames via the LEFT JOIN, then iterate through the results in whatever app you're building, creating the output as you need it.....

However, here's one way of achieving what you want.... still wouldn't recommend it though

-- edit -- this is driven by data so is different to @ikram answer in that you wouldnt need to edit the stored proc as new levels are added

USE tempdb
GO
DROP TABLE IF EXISTS TableA
DROP TABLE IF EXISTS TableB
DROP TABLE IF EXISTS #wibble
GO

CREATE TABLE TableA (
    levelid INTEGER
    , name VARCHAR(10)
)
INSERT INTO TableA
VALUES (1, 'Admin')
    , (2, 'Moderator')
    , (3, 'Agent')

-- SELECT * FROM TableA

CREATE TABLE TableB (
    username VARCHAR(10)
    , levelid INTEGER
)

INSERT INTO TableB
VALUES ('John', 1)
    , ('Sam', 2)
    , ('Tommy', 2)
    , ('Tony', 3)
    , ('Patrick', 3)
    , ('Jimmy', 3)

-- SELECT * FROM TableB


-- table to hold interim results before output
CREATE TABLE #wibble (
    wobble varchar(10)
    , dummyorder integer
)

-- first insert, the levels
INSERT INTO #wibble
SELECT name
    , levelid * 1000 -- pick a number where the gap between numbers of users in levels is sufficient -- could be derived via count
FROM TableA

-- second insert, the users, placed "inside" the relevent level
; WITH users AS (
    SELECT '---' + username as username
        , levelid
        , ROW_NUMBER() over (PARTITION by levelid order by username) as rn --row number of that user in that level
    FROM TableB
)
INSERT INTO #wibble
SELECT username, (levelid * 1000) + rn from users
UNION ALL
SELECT null, (levelid * 1000) + rn + 1
FROM ( -- add a "dummy" row into each level, one number up from the max number of users in that level
    SELECT levelid, max(rn) as rn from users
    GROUP BY levelid
) AS D

-- final output
select wobble from #wibble order by dummyorder