I use SQL Server 2019 to produce a long list of shipping options. This list is produced by a simple query on a table containing the list items, and displayed on a handheld terminal. Users find it cumbersome and time-consuming to navigate the list to find the correct option, and must do so several times per day.
What I want to do is add a category name with a few dashes to the list, which I then display on the hand-held terminal. This will appear as a “page break” of sorts, which will allow them to quickly visually scan down the list.
The challenge here is that the terminal software is very basic and cannot be modified. It can only do one thing, which is to execute the “GetList” stored procedure and read each row from that procedure in order, to create the list which it then displays to the user.
The output must be a single varchar column no more than 64 characters wide, so I’m quite limited in what I can do.
I can modify the data table and query used by the stored procedure, and I’ve already had some success adding CategoryRank and ColumnRank columns and using those to change the order in which the list gets displayed to make more sense to users.
So for example,
Current Query:
SELECT ListItem
FROM SymbolScanner.dbo.ShippingTypes WITH(NOLOCK))
ORDER BY CategoryRank,
ColumnRank
ShippingTypes table:
ListItem varchar(64) NOT NULL PRIMARY KEY CLUSTERED, Category varchar(100) NOT NULL, CategoryRank int NOT NULL, ColumnRank int NOT NULL
Sample Data (the real list is hundreds of items long):
ListItem Category CategoryRank ColumnRank RM 1st Class No Tracking Royal Mail 1 1 RM 1st Class Tracked Royal Mail 1 2 RM 2nd Class No Tracking Royal Mail 1 3 RM 2nd Class Tracked Royal Mail 1 4 TNT 24 Hour Tracked TNT 2 1 TNT 24 Hour No Tracking TNT 2 2 TNT 48 Hour Tracked TNT 2 3 TNT 48 Hour no Tracking TNT 2 4 DHL 24 Hour Tracked DHL 3 1 DHL 48 Hour Tracked DHL 3 1
Current Output:
RM 1st Class No Tracking RM 1st Class Tracked RM 2nd Class No Tracking RM 2nd Class Tracked TNT 24 Hour Tracked TNT 24 Hour No Tracking TNT 48 Hour Tracked TNT 48 Hour No Tracking DHL 24 Hour Tracked DHL 48 Hour Tracked
The result is just a list of varchars, ordered in the way I want them ordered. Which is great, but I want to add a visual break between each category which includes the category name, to make it easier for users to quickly scroll down and see when the category changes.
Desired Output:
---- Royal Mail ---- RM 1st Class No Tracking RM 1st Class Tracked RM 2nd Class No Tracking RM 2nd Class Tracked ---- TNT ---- TNT 24 Hour Tracked TNT 24 Hour No Tracking TNT 48 Hour Tracked TNT 48 Hour No Tracking ---- DHL ---- DHL 24 Hour Tracked DHL 48 Hour Tracked
So what I need is an extra row inserted into my results, which includes the category name and a few dash characters, before each change of category. I’m certain there’s some SQL gymnastics that can be pulled off to do this, I’m just a bit lost at where to even start.
Advertisement
Answer
You could create your header lines in a second query and merge the results by UNIONing:
SELECT ListItem FROM
(
SELECT ListItem, CategoryRank, ColumnRank
FROM ShippingTypes
UNION ALL
SELECT '---- ' + Category + ' ----', CategoryRank, 0
FROM ShippingTypes
GROUP BY Category, CategoryRank
) sub
ORDER BY CategoryRank,
ColumnRank
See this SQLFiddle