SQL: How to include an incrementing column in your query without a cursor or loop

I had the need to do a recursive call that included depth and sort orders. I wanted to reformat the sort orders in a single query without using a cursor or loop.

Turns out there is an easy way with SQL 2005 or higher.

@Folders is a table var from my CTE with Depth and SortOrder in it.

SELECT ID,(ROW_NUMBER() OVER (ORDER BY f.Depth,f.SortOrder,r.SortOrder)) * 100 as SortOrder
FROM someTable r
JOIN @Folders f
ON r.FolderID = f.ID
ORDER BY SortOrder;

Happy Coding!

How to display a complex type/list in a Kendo UI Grid client side using a simple template

I had the need to display multiple values from a list that was attached to each row. Simply put, I had a list of Roles the user could be in for each record. I wanted to display them delimited. Turns out, not that hard.

I at first was not loving the idea of using the Telerik kendo grid. I preferred to roll my own html using javascript alone. Since this is a large team based project, I decided to use a more consistent approach and utilize the Kendo library. Painful at first, since the documentation sucks more than an industrial vacuum.

The beauty lies in the power of the template. Telerik is nice enough to let you use powerful javascript in the templates, which makes it wonderful to configure and tailor each cell to your specifications. For this post, the need was to write a pipe delimited list to the cell of all the role names.

Here is the magic. The template I created. You obviously need to tailor for your field names.

    <script id="rolesColumnTemplate" type="text/x-kendo-tmpl">    

        # for(var iRoleIndex=0;iRoleIndex< Roles.length;iRoleIndex++){ #
        # if(iRoleIndex==0){  #
        #: Roles[iRoleIndex].Name #
        # } else { #
        #: " | " + Roles[iRoleIndex].Name #
        # } } #

You need to bind it to a column which is also easy.

  columns: [

                            field: "Roles", title: "Roles", width: "80px",
                            template: kendo.template($("#rolesColumnTemplate").html()),
                                    operators: {
                                        string: {
                                            eq: "Is equal to",
                                            neq: "Is not equal to"

Notice how easy that was. Well, easy once you know it.  Not easy if you had to figure it out on your lonesome.

Happy Coding!