Concatenating multiple rows into one column in T-SQL

This post looks at how to deliminate many rows into one single column.

I come across this issue quite often so I thought I would share my solution. For example it's quite a good solution for quick ad-hoc data extracts.

There are many ways to do this but the most performant way is actually the simplest too.

The most common approach which most people fall back to would be to iterate through the collection using a Cursor and format a string that way. For many reasons this is not a good idea, from memory to potential deadlock issues to name 2.

For this demo im going to comma deliminate all my tables into a single column for me to then copy this value straight into my favorite text editor to then save as a CSV.

My data looks like the following.

ProductReview
StoreEventPage
AuditLog
Author
TimeLine
TimeLineEvent

Now using the gist below. I can combine the result set of these string values and concatinate them to one string variable.

Now again this is commonly done but note the COALESCE expression. This avoids having duplicated commas and removes null values. The COALESCE expression is a syntactic shortcut for the CASE expression, when the first argument is NULL, COALESCE returns NULL.
The end result is the following which we can now save as a csv.

ProductReview,StoreEventPage,AuditLog,Author,TimeLine,TimeLineEvent

Thanks for reading. Always appreciate hearing your thoughts and comments.
Checkout my website for further info and contact details.

Setting up precompiled handlerbars.js templates and using grunt to compile them

What is Javascript Templating

Good place to start is to describe what exactly JS Templating is and what a good use-case could be. For me, I have data that I've retrieved client side and I need to transform it into repetitive markup then I use a templating library.

Now sure Angular and Knockout are pretty popular approaches for rich client side data-binding but in alot of cases they are too bloated and overkill for certain scenarios.

Say I have a legacy app that uses a heavy amount of JQuery (pretty common scenario for me!) and im tasked to render data from a REST API endpoint to the UI. I dont want to add a whole new client side dependency just to render the data. JS Templating, in particular Handlebars precompiler, is perfect for this scenario.

For more detailed comparisons between templating libraries and performance reviews, please refer to the links below.

• http://viget.com/extend/benchmarking-javascript-templating-libraries
• http://www.sorescode.com/2010/09/12/benchmarks.html

Its not always that straight forward a decision but if you need close control of your markup then its a great approach. There are many alternative approaches, from utilising client side UI controls like Kendo UI or Infragistics Web Controls to previously mentioned Angular JS templates or Knockout data-binding.

The UI controls are an opinionated out-of -the-box solution while Angular and Knockout require their sizable libraries.

Getting Started

The source for this demo can be obtained here. I haven't created any grunt tasks, im just bringing it all together to offer a solution for this demo. 

Using node package manager I install the following packages.

• grunt
• grunt-handlebars-compilar
• grunt-contrib-watch
• handlebars

Please note im using v1.3.0 of the handlebars package.

If you copy the following Package.json into the root directory, then these packages can be added automatically by running the following command.

npm install 

The benefit of using precompiled handlebar templates is that we only now need to use the runtime library. Once minified this is only a 7kb file.
Copy the file to your solution directory. The file should be located in the following location.

\node_modules\handlebars\dist\handlebars.runtime.min.js

Im then going to create a directory for my templates to live and then create my first handlebar template file.

mkdir templates

echo. 2>templates/demo-list.handlebars

The next step is to configure the grunt file. This link will give you further details as to how and why we do this. Below configures the handlebars task with the compile target. I then associate this task to the watch task.
This will now run the handlebars task when a file defined in the watch file pattern is changed and saved.
To test this, run the following command.

grunt watch

And edit the contents of the template.

This should then create scripts/handlebars-templates.js file.

Putting it all together

All thats left to do now is to fetch our arbitrary data and render the markup. To do this ive used githubs free api as you can see from the gist of my gist below.

Finally we add the JS references to our page and the container we want to render the markup inside and were done.

All the code for this demo has been added to github so please feel free to pull it down here.
Any queries please let me know and vist my website http://www.zirafon.org for more contact details.

Thanks for reading. Always appreciate your thoughts and comments.
Checkout my website for further info and contact details.