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

Every time you think you're getting ahead...

So, yesterday I was talking to one of my coworkers about my SQL class in grad school. I was telling him that it was challenging without be difficult, but that some of the problems on our SQL homework were more complicated than I would have expected. As proof, I sited an one of the early example problems we had in the class. We have a database that lists data from a series of foot races held annually for the past decade or so. Dr. Pardue wanted us to report the year of each race, the full name of the winner, the winner's age, and the average age of everyone racing that year. I've never been a SQL guru, but I'm familiar enough with the language that I can muddle through queries better than most of the folks in my class. Even though it took me almost an hour to get working right (mostly because I wasn't paying attention to my data set, and didn't take advantage of the columns available to me), I was pretty proud of myself for getting it done like I did. Here is the query I wrote.

SELECT
YEAR(tr1.RaceTime) As [Year],
trc1.FirstName + ' ' + trc1.LastName As Racer,
trr1.Time,
trr1.Age,
(
SELECT AVG (CAST (trr2.Age AS Float))
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
) AS AverageAgeAll
FROM
tblRace tr1
INNER JOIN tblRaceResult trr1 on trr1.RaceID = tr1.RaceID
INNER JOIN tblRacer trc1 on trc1.RacerID = trr1.RacerID
WHERE
trr1.OverAllRank =
( SELECT MIN (trr2.OverAllRank)
FROM tblRaceResult trr2
WHERE (YEAR(trr2.FinishTime) = YEAR(tr1.RaceTime))
)
ORDER BY tr1.RaceTime


Then I showed the problem to my coworker (who is *much* better at this than I am). He took a quick look at the database and in 5 minutes, had a query that output the exact same answer I had, but in less than half the code it took me. Here's his query:

select
YEAR(rac.racetime) as 'Year'
,ISNULL(rcr.FirstName + ' ', '') + ISNULL(rcr.LastName, '') as Racer
,rr.Time
,rr.Age as Age
,(select AVG(CAST(age AS FLOAT)) from tblRaceResult where raceid = rac.raceid )AS 'Average Age'
FROM
tblRace rac
INNER JOIN
tblRaceResult rr ON rac.RaceID = rr.RaceID AND rr.OverAllRank = 1
INNER JOIN tblRacer rcr ON rr.RacerID = rcr.RacerID
ORDER BY 1

I was almost embarrassed that I didn't think of some of the things he did. Here's a few things I learned from reading my coworker's query:

1. I overthought the problem, when I used the year of the race to link the race table with the raceresults table. They have the RaceID as an FKey relationship, I should have just used that and saved myself the trouble.
2. I didn't know you could append conditions to Inner joins. Notice that James didn't need to use a where clause at all, he just included the "AND OverallRank = '1' " to his join statement.
3. I tend to make things harder than they should be. I need to look for the direct links between tables instead of assuming things are harder than they are, and try to outthink my way past a problem.

Anyway, it just goes to show you that no matter how good you think you're getting, there will always be someone better. I'm just really happy that I'm working with people again who I can learn from.

Rich

Public Speaking, Oh My!

I've been asked by the local LANUG (Lower Alabama .NET User Group) to speak at the next meeting, in September. I've talked it over with Ryan, the organizer, and we decided that the best topic I could present on would be the Social Networking for Developers address that Scott Hanselman gave at Devscovery. I hope he won't be upset. In fact, I need to email him and clear it with him, maybe ask for some help or pointers.

Lets see... what are the topics to cover...
1. Blogging for Devs (Why you should do it and Who you should be reading)
2. Social Networking (Facebook, LinkedIn, Twitter)
3. Community Code Sites (Stack Overflow, Sourceforge)
4. Team Utilities (gist.github.com, pastie.org, etherpad.com)

I should probably go over appropriate vs. inappropriate social activities while on the job and what you should or shouldn't put on a public blog, if I have time. I have to fill an hour's worth of content. I have 3 weeks to prepare for the lecture.

I'm looking forward to it, now I just have to find the time to get my presentation all squared away before then, without falling behind in school or work. Goody!

Rich

Dev Journal: The First Post!

So, this is my developer journal blog. I'm writing this after being inspired to dive deeper into the social networking sphere at the Devscovery conference in Redmond Washington, a couple weeks ago. Scott Hanselman gave the keynote about Social Networks for Developers. He made the case that Developers have come to rely on Social Networking for most of our developer questions. Think about it... when you need to look up the syntax or figure out how to solve a novel problem, what do you do? Do you walk over to your stash of tech books and thumb through the pages, trying to find what you want? No! You Google it (with Bing, if you're Scott)! When you Google your problem, 9 times out of 10, you'll find a post on a forum, a blog post, an article on a developer network, or some other related form of content that was put out on the web by another developer who had to solve the same problem you're now looking in to.

Scott made the case to all of us that keeping a developer blog is valuable to the community, and valuable to yourself. I don't know about any of you, but I have lost count of the number of times I have solved a problem and moved on with my work, only to run into the same or similar problem 6 months later, and don't have a clue how I solved it last time. So, I'm going to be using this blog discuss and archive these developer problems and solutions. When I find something that I think is worth saving, or solve a difficult problem, I will archive the info here.

I'm not a public figure, I'm not a ground breaking developer, I'm just a .NET junkie trying to make my way as a Software Engineer. This journal is more for my benefit than yours, but if you happen to stumble upon this blog and find something that saves your bacon on whatever project you're working on, be my guest. I'm not looking for comments or feedback, but if you have something to contribute to a thread, again... be my guest.

Anyway, that's all I have to say. If you find something that helps you, awesome. If not, back to Google with you!

Richard Akers