Fun with MS SQL spt_values for delimited strings and virtual calendars

By Mark Wills

Not so long ago, there was a fairly tricky question on Experts Exchange to do with unstringing a field, and some very good answers. Not wanting to be left out of the "fray", I submitted one of my old tricks. Despite the code being around for a while in various guises, my posting was met with intrigue and various superlatives. Naturally, I felt rather EElated at the response, though, cannot take all the credit.

So, I thought I would share some of those tricks that center around the use of a rather special table in MS SQL Server known as spt_values in the master database.

First, what is spt_values ?

It has been around since the beginning, it is not a system table, but is very much a part of SQL. It is rather simple, it is Microsoft's very own "look up" table for all those system type things (determined by the TYPE column) where you need a name. More importantly, it is probably always resident in memory.

Let's have a quick peek, and please scroll through the list after you have run the first select command, and you will see quite a variety of "names" which are actually held as numbers within the MS systems...

select * from master..spt_values
                      
                      -- Now, the ones we are interested in are :
                      
                      select number from master..spt_values where type = 'p' 
                      
                      -- which gives us all the numbers from 0 to 2047 inclusive. 

Select all Open in new window

So, what can we do with a list of numbers ?

Well, in this article, we can unstring a delimited field, and we can create a virtual calendar. Both of which might normally involve creating a function or a special table...

Before we get into some examples, we are going to need some data. So, let's create a simple series of tables for Sales and Purchases of Inventory Items. These inventory items have a list of options that can be selected, and it is that list that we want to expand.

create table ee_Inventory (
                         InventID int identity primary key clustered, Product_Code varchar(20), Product_Name varchar(100)
                      )
                      
                      Insert ee_Inventory (Product_Code, Product_Name)
                      select 'R0010','Raw Material 1' union all
                      select 'R0020','Raw Material 2' union all
                      select 'R0030','Raw Material 3' union all
                      select 'F0010','Finished Goods 1' union all
                      select 'F0020','Finished Goods 2' union all
                      select 'F0030','Finished Goods 3' 
                      
                      create table ee_Inventory_Options (
                         OptionID int identity primary key clustered, InventID int, 
                         OptionName varchar(20), OptionList varchar(1000)
                      )
                      
                      Insert ee_Inventory_Options (InventID, OptionName, OptionList)
                      select 1,'Colour','Red,Blue,Green,White' union all
                      select 2,'Colour','Yellow,Blue,Black,White' union all
                      select 3,'Colour','Yellow,Green, Blue, Black,Red, White' union all
                      Select 4,'Colour','White,Yellow,Green, Blue, Black, Red' union all
                      Select 5,'Colour','White,Yellow,Green, Blue, Black, Red' union all
                      Select 6,'Colour','White,Yellow,Green, Blue, Black, Red' 
                      
                      create table ee_Purchase (
                         PurchID int identity primary key clustered, InventID int, Purchase_Date Datetime, 
                         Qty int, Price money, InventoryOption varchar(100)
                      )
                      
                      Insert ee_Purchase (InventID, Purchase_Date, Qty, Price, InventoryOption)
                      select 1,'01 Jun 2009',10,100.00,'Red' union all
                      select 2,'02 Jun 2009',20,200.00,'Blue' union all
                      select 3,'03 Jun 2009',30,300.00,'Black' union all
                      select 1,'11 Jun 2009',10,100.00,'Red' union all
                      select 2,'12 Jun 2009',20,200.00,'Blue' union all
                      select 3,'13 Jun 2009',30,300.00,'Black' union all
                      select 1,'21 Jun 2009',10,100.00,'Red' union all
                      select 2,'22 Jun 2009',20,200.00,'Blue' union all
                      select 3,'23 Jun 2009',30,300.00,'Black' 
                      
                      create table ee_Sales (
                         DetailID int identity primary key clustered, InventID int, Sales_Date DateTime, 
                         Qty int, Price money, InventoryOption varchar(100)
                      )
                      
                      Insert ee_Sales (InventID, Sales_Date, Qty, Price, InventoryOption)
                      select 4,'04 Jun 2009',10,140.00,'Red' union all
                      select 5,'05 Jun 2009',20,250.00,'Blue' union all
                      select 6,'06 Jun 2009',30,360.00,'Black' union all
                      select 4,'14 Jun 2009',10,140.00,'Red' union all
                      select 5,'15 Jun 2009',20,250.00,'Blue' union all
                      select 6,'16 Jun 2009',30,360.00,'Black' union all
                      select 4,'24 Jun 2009',10,140.00,'Red' union all
                      select 5,'25 Jun 2009',20,250.00,'Blue' union all
                      select 6,'26 Jun 2009',30,360.00,'Black' 

Select all Open in new window

Right, so now we have some data, we are ready to begin...

First we will "normalise" the Raw Materials into a precise list of all possible selections. We will do this by "unstringing" the delimited string of options.

Select I.Product_Code,I.Product_Name, O.OptionName
                           , ltrim(SUBSTRING(O.OptionList, number, CHARINDEX(',', O.OptionList + ',',number) - number)) AS Choices
                      FROM ee_Inventory_Options O
                      INNER JOIN ee_Inventory I on I.InventID = O.InventID AND I.Product_Code like 'R%'
                      CROSS JOIN (SELECT number FROM master..spt_values with (nolock) WHERE type = 'P') AS N
                      WHERE SUBSTRING(',' + O.OptionList, number, 1) = ','
                      AND number < LEN(O.OptionList) + 1

Select all Open in new window

Can you see what we did ?

We are using our list of numbers as pointers to the delimiter character. The first part is easy enough, we simply join Inventory items to their list of options. The second part is then using our list of numbers to point to the individual occurrences of the delimiter (hence duplicating the rows for however many delimiters there are). Our select statement takes care of extracting the individual choices by using the value of the pointer as part of the substring command.

Not the most efficient, but no need for any special functions to be written and very handy in some more complex select statements, where it is all taken care of in a single select.

Now there is a "gotcha". Considering our range of numbers has a maximum value of 2047, our string cannot be longer unless we go and create our own "numbers" table.

The next example is based around datetimes. Ever needed a list of days in a month, or, the hours in a day ? Well it is pretty easy to create dynamically with a list of numbers at our disposal. Have a look at the next two select statements using "getdate()". We will do something similar for the next example using our sample data.

-- show all the days in the current month
                      
                      Select dateadd(month,datediff(month,0,getdate()),0) + number
                      from master..spt_values n with (nolock)
                      where number between 0 and day(dateadd(month,datediff(month,-1,getdate()),0) - 1) -1 and type = 'p'
                      
                      -- show all the hours in the current day
                      
                      Select dateadd(hour,number,dateadd(day,datediff(day,0,getdate()),0))
                      from master..spt_values n with (nolock)
                      where number between 0 and 23 and type = 'p'

Select all Open in new window

So, let's put that to good use and show the sales and purchases (i.e., inventory movement) throughout the month. Suppose we are provided the parameter @P as a month / year string. We could use it directly, but better to cast as the start and end of month as appropriate.

declare @P varchar(20)
                      set @P = 'June 2009'           -- our Month Year input parameter - possibly for a stored procedure
                      
                      declare @SOM datetime
                      set @SOM = '01 '+@P            -- our start of month
                      
                      declare @DIM int
                      set @DIM = day(dateadd(month,datediff(month,-1,@SOM),0) - 1)     -- and last day of month
                      
                      
                      Select CalendarDate,sum(isnull(S.qty,0)) as Sales_Qty
                           , sum(isnull(S.price,0)) as Sales_Price, sum(isnull(P.Qty,0)) as Purchase_Qty
                           , sum(isnull(P.Price,0)) as Purchase_Price
                      from (
                         Select @SOM + number as CalendarDate 
                         from master..spt_values n with (nolock) 
                         where number between 0 and @DIM -1 and type = 'p'
                      ) D
                      left outer join ee_Sales S on S.Sales_Date = D.CalendarDate
                      left outer join ee_Purchase P on P.Purchase_Date = D.CalendarDate
                      Group by D.CalendarDate

Select all Open in new window

Yeah, yeah, this could have been done with a CTE Recursive query, except that sometimes, the recursive part is not available for calculating a calendar, and using the above method we can still get our virtual calendar.

So, using a list of numbers can be very handy. The fact that Microsoft supplies them is even handier. Hope you find some inspiration and possible uses for spt_values in your own work. It should be noted however that spt_values is not an officially supported table, and has limitations (0 - 2047 for type 'P'), and maybe you can do better by creating your own dedicated numbers table as previously mentioned.