Clash Royale CLAN TAG#URR8PPP

TSQL: Add lines to “SELECT” result set

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?

JavaScript

2 Answers
2

You need to add one more union :

union

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


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

SELECT

[table]

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.