Skip to content
Advertisement

How can I add a row to my SQL results between each “category” change in the data?

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

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement