<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Fun with MS SQL spt_values for delimited strings and virtual calendars

Published on
37,096 Points
12,596 Views
15 Endorsements
Last Modified:
Awarded
Community Pick
Mark Wills
Love to Help
Give a man a fish and you've fed him for a day; Teach a man to fish and you've fed him for the rest of his life. Be the teacher
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. 

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' 

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

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'

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

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.
15
Comment
Author:Mark Wills
  • 2
4 Comments
LVL 61

Expert Comment

by:Kevin Cross
Mark, nice article.
Made a link from my date article in response to a discussion comment and realized I never officially voted yes above -- you have my vote now!
0
LVL 38

Expert Comment

by:ValentinoV
G'day Mark,

Thanks for showing some ingenious ways of using a list of numbers, great!  Voted Yes.

Cheers,
Valentino.
0
LVL 41

Expert Comment

by:Sharath
Mark, nice article.

Why don't you provide examples on splitting the string, displaying vertically etc. here?

As you have already answered such questions, you can add links to those questions for anyone's reference whoever want to check the magic of spt_values.

-Sharath
0
LVL 61

Expert Comment

by:Kevin Cross
Here is a nice follow-up tip from chapmandew on how spt_values trick from Mark can be utilized even when a higher than 2048 is needed.

http://sqlservernation.com/blogs/tipweek/archive/2010/06/27/quickly-generate-a-number-sequence-in-sql-server.aspx

Again this isn't always the optimal method over say a persisted numbers table, but boy have I had fun with this so just wanted to give kudos to Mark again despite the fact I already relinquished my vote above! :)
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month