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?

You better do all that stuff on the client side. This is a presentation issue, not a data issue, therefor you have no business doing it on the database. — Zohar Peled, Aug 08 '18 at 13:59
"I have no access to the code which creates the list" Are you saying you can't modify the SQL? If you need to do this in JavaScript, why haven't you tagged `JavaScript`? — Tab Alleman, Aug 08 '18 at 14:08
@Tab Alleman nope, I have access to the SQL and I have access to the template, which shows the output. But I have no access to the code which generates the list (it's not part of the template). — Lammi, Aug 08 '18 at 14:10

Yogesh Sharma · Accepted Answer · 2018-08-08T14:04:05.367

0

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 . . . ;

edited Aug 08 '18 at 14:04

answered Aug 08 '18 at 13:57

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Aug 08 '18 at 14:00

Add in a new column to each SELECT in your UNION to determine sort order so you don't have to monkey around with a CASE:

SELECT itemid, ItemDesc FROM 
(
    SELECT '' as ItemId, '-- Please select --' as ItemDesc, 0 as mysortcolumn
    UNION 
    SELECT 
        [id] as ItemId, 
        [DisplayName] as ItemDesc, 
        CASE ItemDesc           
            when 'bla' then 1
            when 'fasel' then 2
            when 'blubb' then 3
            when 'lala' then 4
            when 'duh!' then 5
            when 'spamalot' then 6
            else 7 END as mysortcolumn 
    FROM [table] 
    UNION 
    SELECT '' as ItemId, '-- Please select --' as ItemDesc, 1000 as mysortcolumn
) as t
ORDER BY mysortcolumn asc;

BUT... this feels like you are trying to build a UI in your record set. Are you sure this is the place where you should be doing this logic? It's very cumbersome for a database. It feels like you should be running that ONE SELECT statement against [table] with your order by and then the two beginning and ending lines should be written by your code that builds the UI (wherever this data is being used).

yes, unfortunately I have no access to the code which builds the UI. I know this is a shitty way to change an UI. — Lammi, Aug 08 '18 at 14:04
Oddly I feel a lot better about this if we all agree it's crappy ;) I'm going to leave that last paragraph in this answer even though you are clearly on the up and up, just because I KNOW someone will stumble upon this answer in a year from now that doesn't realize how crappy it is and perhaps will maybe get a clue. (or at least save them from being shanked in the parking lot after work by the person that has to inherit their application). — JNevill, Aug 08 '18 at 14:07

TSQL: Add lines to "SELECT" result set

2 Answers2