How to simulate the MySQL group_concat function in MS SQL Server

The issue: I am writing a web app to track agreements where each agreement can have one, or multiple,  account numbers associated with them, like so:

Agreement No. 1: Account Numbers = XYZ-123-123-123; XYZ-123-123-456; XYZ-123-123-789
Agreement No. 2:  Account Numbers = XYZ-123-123-333
Agreement No. 3: Account Numbers = XYZ-123-123-123; XYZ-123-123-789


I quickly decided to store the account numbers in a separate table so that each agreement could have a variable number of account numbers associated with it:

Agreement_Account_Numbers table
Agreement_ID   Account_Number
1                         XYZ-123-123-123
1                         XYZ-123-123-456
1                         XYZ-123-123-789
2                         XYZ-123-123-333
3                         XYZ-123-123-123
3                         XYZ-123-123-789

But then I ran into the problem of how to display all of an agreements account numbers in a table listing a number of agreements. I wanted an SQL query that would return one agreement per line, with all of the account numbers for each agreement concatenated in a single field.  In MySQL this would be easy: do JOIN query with the agreements table linked by Agreement_ID to the  on the agreement ID and then GROUP BY Agreement_ID and use the group_concat function to concatenate the Account_Number field.  However, for this project I have to work with MS SQL Server and it apparently does not have any such function.  Googling around led me to this excellent question on stackoverflow:

Simulating group_concat MySQL function in MS SQL Server 2005?

It took me a while to sort out which answer to this question was easiest to implement, so I am writing down what worked for me for my own future reference.  Here is what I ended up with:

 SELECT
     Agreement_ID,
     STUFF(
         (SELECT '; ' + Account_Number
          FROM agreement_account_numbers
          WHERE agreement_id = a.agreement_id
          FOR XML PATH (''))
          , 1, 1, '')  AS Account_Numbers
FROM Agreement_Account_Numbers AS a
GROUP BY agreement_id

The result of this query is:

Agreement_ID      Account_Numbers
1                           XYZ-123-123-123; XYZ-123-123-456; XYZ-123-123-789
2                           XYZ-123-123-333
3                           XYZ-123-123-123; XYZ-123-123-789

This query uses a sub-select clause to retrieve all of the account numbers for each agreement by linking the Agreement_Account_Numbers table back to itself (by giving the table the alias "a") and then putting sub-select results in a string using the MS SQL Server specific command FOR XML PATH ('').  Then the STUFF function replaces the first character in the sub-select result string with a zero length string ('') to strip off the leading semi-colon that would otherwise be in front of the first account number.

The command is actually FOR XML with the PATH option specified, and then the tag to surround each row specified to be nothing ('').  I tried figure out exactly now FOR XML works, and what the PATH option means, but as usual I found the MS documentation to be completely cryptic so I just accepted that it works.

Always Google your problem first

If you have a problem that you don't know how to solve, or you need to do something you don't know how to do, the first thing you should always do is just google it.  There are web pages and/or discussions describing how to do or fix just about everything these days, and a large part of the time you can get specific instructions from experts just be googling whatever it is you want to do.

For example, this morning I wanted to put a list of file names from a directory into a document.  Instead of asking someone how to do it, or poking around and trying to figure out how to do it, I just googled "mac os x get copy list of file names to clipboard" and one of the top results was specific instructions on how to do just that.

Another example.  The other day my spouse bought some fresh corn, and we wondered if you could microwave it instead of boiling it.  We googled "microwave corn" and had specific instructions in seconds (it works great; I am never boiling another ear of corn).

This tactic works especially well for computer issues. If you get an error message, the first thing you should do is google it. The majority of the time you will have the solution in seconds.

You can stumble your way through a complex project that you have no idea how to do by using this tactic for each little step.  I taught myself how to write web applications in PHP-MySQL this way.

Many people don't know this trick, so once you know it you can impress your friends and family as an expert on just about everything by using this tactic to instantly finding solutions for their problems.

Don't get it until you really need it

The principle: Don't create or buy something until you know from actual practice exactly why you really need it.
 
This is also known as "You Aren't Gonna Need It"

When people are starting on some endeavor they often put significant effort or money into buying or making things that they think they will need.  However, when it comes time to actually using these things it often turns out that you don't really need it after all, or it's not nearly as useful as you thought it would be, or you need something very different than what you thought you would need. And all the time, money and effort put into acquiring the thing turns out to be wasted.

On the other hand, if you wait until you know from experience that you actually need something, you can acquire exactly what you really need, and you will know how badly you really need it, which will give you a good idea how much time or money you should spend on it.


An example from hiking. I used to spend a lot on hiking boots because I thought I needed them because, by gosh, everyone else used them so they must be neccessary.  Then I experimented with hiking in tennis shoes and sandals, and found that it was more comfortable, I got zero blisters for a change, I suffered fewer twisted ankles, and it was cheaper.  I would have been much better off if I had started out hiking in just whatever shoes I had, and only purchased different shoes when and if I experienced actual problems.