SQL Tricks: Making a Comma Delimited List from a Column in a Table

So, as I was writing the stored proc for a label report, I ran into this situation:  The users would be selecting from a list of possible values in the UI.  When the UI passes that selection back to the database to be run by the Proc, that list gets converted into a list of IDs.  In the stored proc, that list of IDs is exploded back out into a table to get the related information for each ID that the user picked.  However, one of the requirements is that the output from the stored proc contain the plain text list of all of the Name fields associated with those selected IDs.  So, I was had to find a way to take the ‘Name’ column from that exploded table and display every name in the table as a comma delimited list.  As an added twist, the output from each of those names had to be trimmed down to 5 characters, due to space requirements on the finished report.

Let me start by showing how I exploded the list of IDs out into it’s own table, then I’ll show how I collapsed that table back down into a list of names:

   1: --The Stored Proc accepts a list of IDs as an XML String.  I've recreated it here

   2: DECLARE @GradingPeriodID XML

   3: SET @GradingPeriodID = '<Root><Item id="1" /><Item id="2" /><Item id="3" /><Item id="4" /></Root>'

   4:  

   5: --Reading the XML of GradingPeriodIDs into the Temp Table

   6: DECLARE @GradingPeriodIds AS TABLE(Id INT NOT NULL PRIMARY KEY CLUSTERED(Id))

   7: INSERT INTO @GradingPeriodIDs(Id)

   8: SELECT GradingPeriod.id.value('@id', 'SMALLINT')

   9: FROM @GradingPeriodID.nodes('/Root/Item') GradingPeriod(id)

  10:  

  11: --Semicolon is required for CTE

  12: ;

  13: WITH

  14:  

  15: --This CTE exploedes the list of IDs into relevant data

  16: SelectedGradingPeriods

  17: AS

  18: (

  19:     -- No more than 4 grading periods are reported for any label size

  20:     SELECT TOP 4

  21:          SelectedGradingPeriodSequence        = ROW_NUMBER() OVER(ORDER BY gp.StartDate)

  22:         ,GradingPeriodID                    = gp.GradingPeriodID

  23:         ,GradingPeriodCode                    = gp.Code

  24:         ,TermID                                = gp.TermID

  25:         ,AcadSessionID                        = gp.AcadSessionID

  26:         ,GradingPeriodName                    = gp.Name

  27:     FROM

  28:          dbo.GradingPeriod gp

  29:             --LEFT JOIN dbo.StudentGradingPeriod sgp ON sgp.GradingPeriodID = gp.GradingPeriodID

  30:     WHERE

  31:          gp.AcadSessionID = @AcadSessionID

  32:          AND ((@GradingPeriodID IS NULL) OR

  33:               EXISTS (SELECT * FROM @GradingPeriodIds gpi WHERE gpi.Id = gp.GradingPeriodID))

  34:     ORDER BY

  35:         gp.StartDate

  36: )

  37:  

  38: --Here we select the list of Grading Period Names from the CTE

  39: SELECT GradingPeriodList = (

  40:     SELECT SUBSTRING(

  41:         (SELECT ', ' + LEFT(s.GradingPeriodName, 5) 

  42:          FROM SelectedGradingPeriods s FOR XML PATH('')),3,20000) )

Everything above line 38 is background setup for the actual trick of building the comma separated list.  That is done entirely in lines 39-42.  This is done using a couple of neat tricks.

The first trick I use is the ‘FOR XML PATH('')’ query.  Normally, that query is used to build simple XML output code, with the wrapper tag being provided by the parameter passed to the PATH() function.  However, when you structure that query and pass in an empty string, the output comes out without any kind of wrapper around it, and you’re left with a string of values.

The next trick you see there is the SUBSTRING command, followed by the numbers 3, 20000.  That’s a little tricky, but here’s what it does.  If you notice, the query is a concatenation of ', ' and the grading period name.  This concatenation forces a comma to be inserted between every element in the list.  However, that means that the list will start with a comma, instead of the first value in the list.  That’s why we take a substring, starting from the 3rd character and going out for 20000 characters (you can change this value to whatever you need it to be, but setting a very large value ensures you won’t clip your own data).  That substring command effectively trims away the leading comma.

The last part of that query is the LEFT command, which grabs the left most X characters of the value being selected.  In this case, I’ve specified 5.

Combining those 3 tricks together into those 4 lines of code effectively take an entire column of of a table and condense it down into a comma separated list.

A Generic Type Test (in Visual Basic)

I was working on a validation routine recently that involved checking to make sure the string data I was pulling out of an input file was the expected data type.  The input file contained a series of comma delimited lines of text and each line was comprised of a combination of strings, numbers, dates, and boolean values.  Each line had about 30 elements, and I needed to validate every one of them.  In VB, you would do this by using a try catch block and trying to cast the given string value to the expected type.  If the cast worked, your data is valid.  If you get an invalid cast exception (or just a straight up null ref), your cast is invalid.  Sounds easy enough, but I really didn’t want to have to copy the same try/catch block thirty times in a for loop.  So, I wrote the following method:

   1: Private Function CheckType(Of T)(ByVal input As Object) As Boolean

   2:         Dim isType As Boolean = False

   3:  

   4:         Try

   5:             Dim tmp As Object = CType(input, T)

   6:             isType = True

   7:         Catch ex As Exception

   8:             isType = False

   9:         End Try

  10:  

  11:         Return isType

  12:     End Function

The idea here is that you can pass in the string you want to check (input) and the type you want to check it against (T).  I had a terrible time trying to get the type parameter to pass in properly, but thanks to a little help from Justin Spradlin, we got it sorted out.  Now, I can make a quick call to this method right in the middle of an assert function, saving a tremendous amount of code and effort.  Here’s a sample showing how to call the method.  I’ve ripped out most of the assert method call to simplify the code:

   1: If Not dom.Assert((CheckType(Of String)(currentLineArray(0))), ...) Then hasErrors = True

   2: If Not dom.Assert((CheckType(Of String)(currentLineArray(1))), ...) Then hasErrors = True

   3: If Not dom.Assert((CheckType(Of DateTime)(currentLineArray(2))), ...) Then hasErrors = True

   4: If Not dom.Assert((CheckType(Of Boolean)(currentLineArray(3))), ...) Then hasErrors = True

   5: If Not dom.Assert((CheckType(Of Integer)(currentLineArray(4))), ...) Then hasErrors = True

With a little effort, this function can be modified for C#.  It could be as simple as casting the input object as type T in the try/catch block, or even using the ‘Is’ operator.  I’m not sure if the method signature is the same in C# either, as I’ve never tried to pass a Type as a parameter in C#, but I don’t imagine it would take much effort to figure it out.

Some useful Regex Tips and Tricks

So, I’ve never been a wiz at Regex, but I’ve found myself using it more and more lately for projects at work.  The most recent case was when I had to validate some time data in a text field, to ensure it was in proper 12 hour format with an AM/PM suffix.  There was an existing method to do that validation in our code base, but it was hard to follow and it was returning false positives if you passed in plain integer values.  So, instead of trying to fix the malfunctioning code, I simply wrote a new validator that was based on a proven Regular Expression.  At the end of the day, my RegEx validator worked like a charm, and it was super easy to use.  Here’s the entire ValidateTime() Method:

   1: /// <summary>

   2: /// Validates the time against a regular expression that ensures standard military or 

   3: /// standard AM/PM time formats. Returns true or false based on the input time span.

   4: /// </summary>

   5: /// <param name="timeSpan"></param>

   6: /// <returns></returns>

   7: public static bool ValidateTime(this TimeSpan timeSpan)

   8: {

   9:     bool validTime = false;

  10:     string tmpTime = timeSpan.ToString();

  11:  

  12:     Regex validTimePattern = new Regex(@"^((([0]?[1-9]|1[0-2])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?( )?(AM|am|aM|Am|PM|pm|pM|Pm))|(([0]?[0-9]|1[0-9]|2[0-3])(:|\.)[0-5][0-9]((:|\.)[0-5][0-9])?))$");

  13:  

  14:     validTime = validTimePattern.IsMatch(tmpTime);

  15:     return validTime;

  16: }

That’s the joy of RegEx.  The old method was 50+ lines of code and it wasn’t reliable.  Of course, you have to be familiar with RegEx in order to make a method like this work… or do you…?

Well, it turns out, you really don’t.  I would be hard pressed to write a regular expression that was more complicated than, say, a simple 7 digit phone number.  I certainly didn’t have the wherewithal to write the above monstrosity of RegEx.  So, where did it come from?  The answer is hardly surprising.  I’ve had need of regular expressions before, and inevitably I always wind up going to the following site to get them:

RegEx Library

This place has a searchable interface, good categorization of expressions, and thousands upon thousands of regular expressions to choose from.  Many times there are dozens of expressions that perform the same checks, or perform very similar checks.  Unfortunately, that can cause trouble if you aren’t careful, so it’s always a good idea to double check your regular expressions before you put them into production.  I do that with the help of this site:

JavaScript Regular Expression Editor

That site is a life saver.  It’s very simple to use:  Simply enter your Regular Expression in the top box, enter the string you wish to validate in the middle box, and observe your results in the bottom box.  With a few minutes on this site, you’ll have a pretty good idea if your expression will work for you or not.  Remember to test for things like case, length, and invalid characters.  Once you have a regular expression that you are confident will work for your situation, you can use the code shown above to craft a very simple check to validate your input.

Rich

Home Server Setup

Oooooh, something useful to put on my Developer blog!  Awesome!

So, I just moved my Dell PowerEdge 700 into the living room last night, hiding it behind my overly massive TV.  I long ago set it up to be a personal development server, but I abandoned the project in frustration last summer when I couldn't get a wireless card to work with it and Windows Server 2008.  I don't have enough power or space near the rest of my computers to leave it in proximity to the router, so I just shut the machine down and moved on without it.

Well, a few months back I bought a new pair of Sony Home Electronics (HDTV and BluRay player), both of which are network ready.  Neither had wifi built in, but they had ethernet jacks.  So, I got a 2 port Netgear wireless N bridge.  I've had it plugged into the TV and BluRay player up until now.  However, I never really use the internet features on the TV, because I don't have sound running from the TV to the surround sound.  I just use the BluRay player for all my network streaming, and it works out just fine.

I've begun work on a personal project, building a website for a local organization.  I need, at a minimum, a place to store my code (i.e. VSS at least, TFS at best).  Since the Dell already had Windows Server 2008 and SQL Server 2008 loaded up on it, and it was just sitting there anyway, I went ahead and moved it to the alcove behind the TV, fired it up, ran windows update, and got RDP working on it.  While that was going on, I downloaded VSS and TFS from my MSDNAA account, and I'm going to see about setting those services up over the next week or so.

So, hopefully I'll be able to document my TFS setup process here.  I'm not real sure what all TFS is supposed to do, and i'm sure it's way overkill for 1 man projects, but being familiar with it will be a valuable skill at my office, which is in the process of rolling TFS into production this year.  Having VSS for the interim while I work on the TFS install will be helpful as well.

Rich