TSQL: Add lines to "SELECT" result set

Question

we're using SQL Server 2017 Express and I have this SELECT statement:

SELECT * FROM 
(SELECT '' as ItemId, '-- Please select --' as ItemDesc
    UNION 
    SELECT [id] as ItemId, [DisplayName] as ItemDesc 
    FROM [table] 
) as t
ORDER BY
CASE ItemDesc
    when '-- Please select --' then 1
    when 'bla' then 2
    when 'fasel' then 3
    when 'blubb' then 4
    when 'lala' then 5
    when 'duh!' then 6
    when 'spamalot' then 7
    else 8
end, ItemDesc

This works, but I need to add a second "static" line at pos. 8, something like

SELECT * FROM 
    (SELECT '' as ItemId, '-- Please select --' as ItemDesc,
     '' as ItemId, '----------' as ItemDesc
        UNION 
    ...
CASE ItemDesc
    ...
        when '----------' then 8
        else 9

Of course this doesn't work, but you get the idea. Unfortunately I have no access to the code which creates the list, all I could do is to add Javascript to the output.

Is this somehow possible? And does it make any sense to do this NOT using JS and manipulate the DOM?

Answer 1

You need to add one more union :

SELECT t.*
FROM (SELECT '' as ItemId, '-- Please select --' as ItemDesc
      UNION 
      SELECT '', '----------' 
      UNION
      SELECT [id] , [DisplayName] 
      FROM [table] 
     ) t
ORDER BY . . . ;