How to escape from the View designer in MS SQL Server Management Studio (SSMS)

I started working with SQL Server, and MS SQL Server Management Studio about a year ago, and I quickly got frustrated with the View designer module.  If you click on "New View" in the object explorer, or if you right click on an existing View and choose "Design," it opens up this multi-pane view designer with a graphical representation of the View on top, followed by some table listing the View elements, followed by the SQL for the view formatted as nearly impenetrable blocks of code.  For simple Views the View designer was fine, but for complicated Views I came to hate working with it.  I especially hated the fact that if I tried to format the SQL in a more readable layout, the View designer would throw out my formatting the next time I opened the View in Design mode.

I finally decided that there had to be a better way, and thanks to some discussions on Stack Exchange (natch) I pieced together the following:

• You can edit a View in plain SQL, and have your layout and formatting preserved when you save, by doing the following:
• Right click the View
• Select Tasks -> Script View as -> ALTER To -> New Query Editor Window
• Make your edits in the resulting Query Editor window
• Hit Execute when you are done to save your changes.  The ALTER TO command replaces the current version of the View with your edited version.
• You can quickly clean up the brain dead SSMS formatting of the SQL of existing Views using the free poorsql.com website. You block and copy the ugly SQL into the window, choose from various formatting styles, and then the site instantly prepares a properly formatted version of the SQL.

How to export a table as a SQL script using Microsoft SQL Server Management Studio

I come from the PHP-MySQL world, so I am used to being able to quickly generate a backup of a table as a SQL script (CREATE + INSERT) using the easy to find Export function in phpMyAdmin.  When I had to  use SQL Server for a project I was a bit puzzled when I couldn't find a similar feature in Microsoft SQL Server Management Studio (MS-SSMS).  However, a bit of Googling revealed the (cumbersome) solution:

• In the Object Explorer in MS-SSMS right click the database that has the table you want to export.
• Select Tasks from the right-click menu.
• Select Generate Scripts from the sub menu, which will open a pop-up called Generate and Publish Scripts.
• Click Next on the Introduction screen, which takes you to the Choose Objects screen
• On the Choose Object screen select the "Select specific database objects" option
• Expand the Tables object by clicking the little plus sign
• Select the table(s) you want to export
• Click Next, which takes you to the Set Scripting Options screen.
• Choose where you want the output to go (file, clipboard or new query window)
• Click the Advanced button, which launches a pop-up window called "Advanced Scripting Options" with a list of options
• Scroll down to the "Types of data to script" option, which is at the very bottom of the set of options called  "General."
• Change the "Types of data to script" option from "Schema only" to "Schema and data."  This is what will make the SQL include the data from the table.
•  Click OK to close the Advanced Scripting Options pop-up.
• Click Next on the Set Scripting Options screen.
• Click Next on the Summary screen where it says "Review your selections."  This is what triggers the actual export of the table.
• Click Finish to close the Generate and Publish Scripts pop-up.

There, was that so bad? Well, yes, it was a lot of clicking for something that people probably do relatively frequently, but that is Microsoft for you.

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.