Reducing Code by Using jQuery Templates

Reducing Code by Using jQuery Templates

imageNearly every language out there uses templates in some shape or form to minimize the amount of code that has to be written in an application. By using templates you can define a template structure once and use it to generate code, HTML or other formats. If you’ve created ASP.NET applications then you’re aware of how powerful and productive templates are when it comes to generating HTML output. However, what if you want to use templates on the client-side to generate HTML rather than writing a lot of JavaScript to manipulate the DOM?

Although templates have been available in jQuery for quite awhile through various plug-ins, the latest template framework worked on jointly by Microsoft and the jQuery team provides a great solution that doesn’t use CSS in strange ways or require a lot of knowledge about a template language. By using it you can define HTML templates in web pages and use them to render HTML output dynamically at runtime. I’ll cover the basics of using the jQuery Templates in this post but you can get additional information fromhttp://api.jquery.com/category/plugins/templates.

You can download the jQuery Templates script here along with a sample application or reference one of the following Microsoft CDN scripts:

Defining a Template Block

Templates are defined by referencing the template script mentioned above and then defining a <script> block in your page with a type of text/x-jquery-tmpl as shown next:

 

<script id="OrderSummaryTemplate" type="text/x-jquery-tmpl">
    <!-- Template goes here –>
</script>

 

Once the script tag is defined you can place template code inside of it. Any HTML that you add is output automatically once the template is rendered. Of course, adding static HTML doesn’t accomplish much so jQuery Templates provides a template tag language that contains several tags that can be placed inside of a template to define data that should be output, perform conditional logic, iterate through items, render nested templates, plus more. The different template tags available with jQuery Templates are are shown next (descriptions and some of the examples are from the jQuery Templates documentation):

Template Tag Example Description
${fieldNameOrExpression} ${ DeliveryFee } Used for insertion of data values in the rendered template. Evaluates the specified field (property) on the current data item, or the specified JavaScript function or expression.
{{html fieldNameOrExpression}} {{html Comments}} Used for insertion of HTML markup strings in the rendered template. Evaluates the specified field on the current data item, or the specified JavaScript function or expression.
{{if}}
{{if DeliveryFee > 0}}
   ${ DeliveryFee } added to your order.
{{/if}}
Used for conditional insertion of content. Renders the content between the opening and closing template tags only if the specified data item field, JavaScript function or expression does not evaluate to false (or to zero, null or undefined).
{{else}}
{{if MainItems.length==0}}
    <tr>
        <td>No items selected</td>
    </tr>
{{else}}
    <tr>
        <td>Ordered items!</td>
    </tr>    
{{/if}}
Used in association with the {{if}}…{{/if}} tag to provide alternative content based on the values of one or more expressions. The {{else}} tag can be used without a parameter, as in: {{if a}}…{{else}}…{{/if}}, or with a parameter, as in: {{if a}}…{{else b}}…{{/if}}.
{{each}}
{{each(i,mi) MainItems}}
    <tr>
        <td>
            ${ mi.NumberOrdered } ordered 
            at $ ${ mi.Price} per item
        </td>
    </tr>
{{/each}}
Used to iterate over a data array, and render the content between the opening and closing template tags once for each data item.
{{tmpl}}
<script id="movieTemplate" type="text/x-jquery-tmpl"> 
    {{tmpl "#titleTemplate"}}
    <tr>
       <td>${ Director }</td>
    </tr>
</script>

<script id="titleTemplate" type="text/x-jquery-tmpl"> 
    <tr>
        <td>${ Name }</td>
    </tr>
</script>
Used for composition of templates. Renders one or more nested template items within the rendered output of the parent template.
{{wrap}}
<script id="myTmpl" type="text/x-jquery-tmpl">
    The following wraps some HTML content:
    {{wrap "#tableWrapper"}}
        <div>
            First <b>content</b>
        </div>
        <div>
            And <em>more</em> <b>content</b>...
        </div>
    {{/wrap}}
</script>

<script id="tableWrapper" type="text/x-jquery-tmpl">
    <table><tbody>
        <tr>
            {{each $item.html("div")}}
                <td>
                    {{html $value}}
                </td>
            {{/each}}
        </tr>
    </tbody></table>
</script>
Used for composition of templates which incorporate wrapped HTML content. Rendered template items can combine wrapped HTML content with template markup.

 

Rendering a Template

Once a template is defined using a <script> block you can use the jQuery Templates tmpl() function to render the template to a container. This is done by identifying the target template, calling tmpl and passing a JSON object into it and then defining where the rendered output should go:

$('#OrderSummaryTemplate').tmpl(json).appendTo('#OrderSummaryOutput');

The JSON data can be created locally or retrieved from a remote service call as shown next:

 

$.ajax({
    dataType: 'jsonp',
    url: moviesServiceUrl,
    jsonp: '$callback',
    success: showMovies
});

// Within the callback, use .tmpl() to render the data.
function showMovies(data)
{
    // Render the template with the "movies" data and insert
    // the rendered HTML under the 'movieList' element
    $('#movieTemplate').tmpl(data).appendTo('#movieList');
}

jQuery Templates in Action

A sample application that I created to demonstrate jQuery Templates in action can be downloaded here (it’s part of the sample code available with our jQuery and AJAX Programming training course). The sample app is an ASP.NET MVC 2 project named “Order Up” that leverages jQuery heavily and uses jQuery Templates to render order details. An example of the output that’s rendered is shown next:

image
The template used to generated the Totals, Delivery Information, Items Ordered and Accessories Ordered sections is shown next:

 

<script id="OrderSummaryTemplate" type="text/x-jquery-tmpl">
    <table style="width:100%;">
        <tbody>             
            <tr>
                <td>Totals:</td>                    
            </tr>                    
            <tr>
                <td style="font-size:12pt;">
                    <table style="width:400px;">
                        <tr>
                            <td style="width:50%;">Sub Total:</td>
                            <td>$<span id="FinalSubTotal">${ FinalSubTotal }</span></td>
                        </tr>
                        <tr>
                            <td style="width:50%;">Sales Tax:</td>
                            <td>$<span id="FinalSalesTax">${ FinalSalesTax }</span></td>
                        </tr>
                        {{if DeliveryFee > 0}}
                            <tr>
                                <td style="width:50%;">Delivery Fee:</td>
                                <td>$<span id="FinalDeliveryFee">${ DeliveryFee }</span></td>
                            </tr>
                        {{/if}}
                        <tr>
                            <td style="width:50%;">Admin Fee:</td>
                            <td>$<span id="FinalAdminFee">${ AdminFee }</span></td>
                        </tr>
                        <tr style="border-top:1px solid black;">
                            <td style="width:50%;font-weight:bold;">Total:</td>
                            <td>$<span id="FinalTotal">${ FinalTotal }</span></td>
                        </tr>   
                        <tr>
                            <td colspan="2">&nbsp;</td>
                        </tr>
                        <tr>
                            <td colspan="2">Will be charged to your credit card ending with ${ CreditCard }</td>
                        </tr>                     
                    </table>                        
                </td>
            </tr> 
            <tr>
                <td>&nbsp;</td>
            </tr> 
            <tr>
                <td>Delivery Information</td>                    
            </tr>
            <tr>
                <td>
                    <table style="width:500px;">
                        <tr>
                            <td style="width:25%;">Deliver To:</td>
                            <td>${ DeliveryName }</td>
                        </tr>
                        <tr>
                            <td style="width:25%;">Address:</td>
                            <td>${ DeliveryAddress }</td>
                        </tr>
                        <tr>
                            <td style="width:25%;">Date and Time:</td>
                            <td>${ DeliveryDate } from ${ DeliveryTime }</td>
                        </tr>                        
                    </table>                         
                    </td>
            </tr>
            <tr>
                <td>&nbsp;</td>
            </tr>                    
            <tr>
                <td>Items Ordered</td>
            </tr> 
            {{if MainItems.length==0}}
                <tr>
                    <td>No items selected</td>
                </tr>
            {{else}}
                {{each(i,mmi) MainItems}}
                    <tr>
                        <td>
                            ${ mmi.Name } - ${ mmi.NumberOrdered } ordered at $ ${ mmi.Price } per item
                        </td>
                    </tr>
                {{/each}}
            {{/if}}
            <tr>
                <td>&nbsp;</td>
            </tr>   
            <tr>
                <td>Accessories Ordered</td>
            </tr> 
            {{if AccessoryItems.length==0}}
                <tr>
                    <td>No items selected</td>
                </tr>
            {{else}}
                {{each(i,ai) AccessoryItems}}
                    <tr>
                        <td>
                            ${ ai.Name  } - ${ ai.NumberOrdered } ordered at $ ${ ai.Price } per item
                        </td>
                    </tr>
                {{/each}}
            {{/if}}
            <tr>
                <td>&nbsp;</td>
            </tr>                          
        </tbody>
    </table>    
</script>

The template is rendered to a div with an ID of OrderSummaryOutput using the following code.  The code first creates a JSON object by retrieving data from controls in a checkout wizard and then calls the jQuery Templates tmpl() function:

 

function LoadApprovalDiv()
{
    var subTotal = parseFloat($('#SubTotal').text());
    $('#ClientSubTotal').val(subTotal.toFixed(2));
    var salesTaxRate = parseFloat($('#SalesTaxRate').val()) / 100;
    var salesTaxAmount = (subTotal * salesTaxRate) * .9;
    var deliveryFee = parseFloat($('#DeliveryFee').val());
    var adminFee = ((subTotal + salesTaxAmount + deliveryFee) * .05);
    var total = (Round(subTotal) + Round(salesTaxAmount) + Round(deliveryFee) + 
                 Round(adminFee));
    $('#ClientTotal').val(total);
    var deliveryAddress = $('#Delivery_Street').val();
    //See if they entered a suite
    if ($('#Delivery_Suite').val() != '') deliveryAddress += ', Suite ' + $('#Delivery_Suite').val();
    deliveryAddress += ' ' + $('#Delivery_City').val() + ' ' + $('#Delivery_StateID option:selected').text() + ' ' + 
                       $('#Delivery_Zip').val();
    var creditCard = $('#Payment_CreditCardNumber').val();
    var abbrCreditCard = '*' + creditCard.substring(creditCard.length - 5);

    var json = {
                   'FinalSubTotal'  : subTotal.toFixed(2),
                   'FinalSalesTax'  : salesTaxAmount.toFixed(2),
                   'FinalTotal'     : total.toFixed(2),
                   'DeliveryFee'    : deliveryFee.toFixed(2),
                   'AdminFee'       : adminFee.toFixed(2),
                   'DeliveryName'   : $('#Delivery_Name').val(),
                   'DeliveryAddress': deliveryAddress,
                   'CreditCard'     : abbrCreditCard,
                   'DeliveryDate'   : $('#Delivery_DeliveryDate').val(),
                   'DeliveryTime'   : $('#Delivery_DeliveryTime option:selected').text(),
                   'MainItems'      : GenerateJson('Main'),
                   'AccessoryItems' : GenerateJson('Accessory')
               };

       $('#OrderSummaryOutput').html('');
       //jQuery template example
       $('#OrderSummaryTemplate').tmpl(json).appendTo('#OrderSummaryOutput');

}

If you’re working with dynamic web applications that leverage jQuery and AJAX you’ll find that the new jQuery Templates plug-in can significantly increase your productivity and eliminate a lot of code that you’d normally have to write. It’s a great tool to have in your jQuery arsenal!

 

 

Logo_702D5F60[1]

If you or your company is interested in training, consulting or mentoring on jQuery or .NET technologies please visit http://www.thewahlingroup.com for more information. We’ve provided training, consulting and mentoring services to some of the largest companies in the world and would enjoy sharing our knowledge and real-world lessons learned with you.

 

Advertisements

ASP.NET MVC from Basics to Tips and Tricks

I had a great time speaking at the Fort Smith .NET User Group last week.  ASP.NET MVC is a subject that I am very passionate about.  I recently had the pleasure of developing an e-commerce website for Wolff Wire – Office Organizers using this technology.  Since then I have been using it anytime I can.  The development feels so much cleaner, and the code is more organized than in ASP.NET WebForms.  In addition the HTML output is not cluttered with ViewState and ClientIDs.

Unfortunately I wasn’t able to get quite as far as I wanted with the presentation, so I figured that I would go ahead and hit the highlights of my presentation in this blog post.

I am not an expert, so if anyone has better ways of doing things please let me know.

The Basics

First off MVC stands for Model-View-Controller.  Below some of the basic components are listed.

  • Model = Data / State
  • View = Responsible only for rendering the HTML output (.aspx page)
  • Controller = Presentation Logic (class with action methods)
    • HTTP operations are routed here
    • Responsible for selecting the appropriate View
    • Provides the View with the proper Model
  • Routing = URL Processing Engine
    • Determines based on the URL what Action Methods to call on the Controller
    • Default URL Structure = Controller Prefix/Action/ID = ex. Product/Detail/2
    • Very Customizable
  • Html Helpers = Methods that generate html (used in View)
    • Partially equates to WebForms Controls
    • Encapsulates more advanced rendering logic outside of the View
    • Html.ActionLink is very important
      • ex. Html.ActionLink( DisplayText, Action, Controller, new {ID or other defined value as property of an anonymous type}, new {anchor tag html attribute defined as a property of an anonymous type})

 

Tips and Tricks / Best Practices

  1. Use Html.ActionLink
    Do not manually create anchor tags, because if the routing configuration is changed your links will be broken.  Html.ActionLink automatically renders appropriate URLs based on the current routing configuration.
  2. Use Descriptive Keyword Rich Names Instead of Database Table IDs (SEO) In URLs
    This is particularly useful for public facing websites such as blogs or e-commerce sites.  Google and other search engines index keywords in URLs, and ID numbers yield no benefit.  Again the default URL structure in ASP.NET MVC is “Controller Prefix/Action/ID” (Product/Detail/2).  There is nothing stating that “ID” has to be an integer.  You could have something like “Product/Detail/paper-tray”.  Just be sure that the controller action methods “ID” parameter is typed as a string.  I generally keep an indexed column in my database for this.  It is a lower case variation of the display name with dashes in place of spaces.  I suppose you could also use a lookup dictionary that maps to the table ID instead.
  3. Configure Routing to Optimize URLs
    Don’t feel bound to the default URL routing configuration.  The routing is very customizable as seen below.

    routes.MapRouteLowercase(
        "Catalog",                                             
        "workspace-organizers",                          
        new { controller = "Category", action = "Index" } 
    );
    //ex. /workspace-organizers
    
    routes.MapRouteLowercase(
        "ProductCategory",                                             
        "{urlname}/cat",                          
        new { controller = "Product", action = "Index", urlname = "" } 
    );
    //ex. /desktop-accessories/cat
    
    routes.MapRouteLowercase(
        "ProductDetail",                                              
        "{urlname}/prod",                         
        new { controller = "Product", action = "Details", urlname = "" }  
    );
    //ex. /cd-holder/prod
  4. Use Strongly Typed Models
    Avoid using hard coded “Magic” strings whenever possible.  One way to send model data to the view is through the use of the ViewData dictionary object.

    public ActionResult Index()
    {
        ViewData["Message"] = "Welcome to ASP.NET MVC!";
        return View();
    }
    <h2><%=
    
    Html.Encode(ViewData["Message"])

    %></h2>

    This is problematic for a number of reasons.  Errors from typos and broken references when refactoring may not show up until runtime.  Also complex objects stored in the dictionary will have to be typed in the view in order to access their properties.  It is best to give your view a specified model type as shown below.

    public ActionResult Index()
    {
        string strMessage = "Welcome to ASP.NET MVC!";
        return View(strMessage);
    }

    Next in the view give the page declaration’s inherits attribute a type.

    Inherits="System.Web.Mvc.ViewPage<string>"

    Then you can utilize the “Model”, which is the instance of the specified type.

    <h2><%=
    
    Html.Encode(Model)

    %></h2>

  5. Use ViewModels
    Often it is necessary to have more than one type represented as model data.  I prefer to have a model per view that consolidates all the required types.

    public class ProductIndexViewModel : ViewModelBase
    {
        public string CategoryName { get; set; }
        public IEnumerable<CatalogListItem> CatalogItems { get; set; }
    }
    public ActionResult Index(string id)
    {
        var viewmodel = new Models.ProductIndexViewModel();
        viewmodel.CatalogItems = productRepository.GetCatalogItems(id);
        viewmodel.CategoryName = categoryRepository.GetCategoryName(id);
        return View(viewmodel);
    }
  6. Use a Master ViewModel
    How do you get data to a master page so it can be used across multiple pages?  A shopping cart summary is one example of where this is needed.

    One option is to have a master controller that all relevant controllers would inherit.  The master controller could set a value in the ViewData dictionary.  Again I don’t like this because it isn’t strongly typed.

    Another option is to use RenderAction to simulate an http request and return a partial view rendered to html.  This has some advantages in that sections of a page can be cached.

    The technique I like to use is to create a master viewmodel that the other models inherit.  Master pages can have a specified model type just like normal views, so I set the master page’s type to the view model base.  This will work as long as every view that implements the master page receives a viewmodel that inherits from the master view model.

    public class ViewModelBase
    {
        public ViewModelBase()
        {
            SiteHeaderText = "MVC Outdoor Catalog";
        }
    
        public string SiteHeaderText { get; set; }
    }
    <%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage<MvcCatalog.Models.ViewModelBase>" %>
    <h1><%= Html.Encode(Model.SiteHeaderText) %></h1>
  7. Use Custom HTML Helper Extensions
    Use html helper extension methods for complex rendering logic.  I first started using this technique when I needed to add some functionality to navigation menu items on a master page.  Initially it was an unordered list containing links generated using Html.ActionLink.  The current page’s menu item needed to have a different CSS class.  Custom HTML Helper to the rescue.  (Originally I found a variation of this at http://www.asp.net/learn/mvc/tutorial-27-cs.aspx )

    public static class MenuItemHelper
    {
        public static string MenuItem(this HtmlHelper helper, string linkText, string actionName, string controllerName)
        {
            string currentControllerName = (string)helper.ViewContext.RouteData.Values["controller"];
            string currentActionName = (string)helper.ViewContext.RouteData.Values["action"];
    
            // Add selected class
            if (currentControllerName.Equals(controllerName, StringComparison.CurrentCultureIgnoreCase) && currentActionName.Equals(actionName, StringComparison.CurrentCultureIgnoreCase))
                return string.Concat("<li class=\"selected\">", helper.ActionLink(linkText, actionName, controllerName), "</li>");
    
            // Add link
            return string.Concat("<li>", helper.ActionLink(linkText, actionName, controllerName), "</li>");
        }
    }
    <ul id="menu">              
        <%= Html.MenuItem("Home", "Index", "Home")%>
        <%= Html.MenuItem("Catalog", "Index", "Category")%>
        <%= Html.MenuItem("About", "About", "Home")%>
    </ul>
  8. Use Custom Routing Extensions
    I like all my URLs to be lower case, but I don’t want to change my controller and action methods to be lower case.  The use of a RoutingCollection extension method easily solves this.  Found this nifty extension method at: http://goneale.com/2008/12/19/lowercase-route-urls-in-aspnet-mvc/

    routes.MapRoute(
        "Default",                                            
        "{controller}/{action}/{id}",                         
        new { controller = "Home", action = "Index", id = "" }
    );

    Using the normal “MapRoute” method as in the snippet above would create a URL structure like “Product/Detail/binder-holder”.

    routes.MapRouteLowercase(
        "Default",                                            
        "{controller}/{action}/{id}",                         
        new { controller = "Home", action = "Index", id = "" }
    );

    Using the modified “MapRouteLowerCase” extension method creates a URL like “product/detail/binder-holder”.

  9. Separate Data Access Logic and Business Logic from the Controller
    The controller is really just for presentation layer management logic.  It should decide what view gets rendered and hand that view the appropriate model data.  I prefer to use the repository pattern to separate the data access logic from the controller.  I also like to use a repository interface so that it can be swapped easily with a different data access method without affecting the controller.  In this case I used manual dependency injection, but an IOC framework could be used.

    public interface ICategoryRepository
        {
            void Add(MvcCatalog.Models.Category category);
            System.Collections.Generic.IEnumerable<MvcCatalog.Models.Category> GetCategories();
            string GetCategoryName(string id);
            void Save();
        }
    ICategoryRepository categoryRepository = new CategoryRepositoryLinqToSQL();
    
    public ActionResult Index()
    {
        var viewmodel = new Models.CategoryIndexViewModel();
        viewmodel.Categories = categoryRepository.GetCategories();
        return View(viewmodel);
    }
  10. Cache Your Data
    A substantial performance gain can be made by not hitting your database for every request.  Again here is another good reason to use the repository pattern.

    public class CategoryRepositoryCached : ICategoryRepository
    {
        private const string cacheName = "Categories";
        ICategoryRepository _repository;
    
        public CategoryRepositoryCached() 
            : this(new CategoryRepositoryLinqToSQL())
        {}
    
        public CategoryRepositoryCached(ICategoryRepository repository)
        {
            _repository = repository;
        }
    
        #region ICategoryRepository Members
    
        public void Add(Category category)
        {
            _repository.Add(category);
        }
    
        public IEnumerable<Category> GetCategories()
        {
            var categories = (IEnumerable<Category>) HttpContext.Current.Cache[cacheName];
            if (categories == null)
            {
                categories = _repository.GetCategories();
                HttpContext.Current.Cache[cacheName] = categories;
            }
    
            return categories;
        }
    
        public string GetCategoryName(string id)
        {
            return _repository.GetCategoryName(id);
        }
    
        public void Save()
        {
            _repository.Save();
            HttpContext.Current.Cache.Remove(cacheName);
        }
    
        #endregion
    }
  11. jQuery + JSON Action Methods = Cool
    It is easy to return a JSON object instead of a view.

    public JsonResult Create(string CategoryName)
    {
        var category = new Models.Category();
        category.Name = CategoryName;
        category.URLName = CategoryName.ToLower().Replace(" ", "-");
        categoryRepository.Add(category);
        categoryRepository.Save();
    
        return Json(category);
    }
    <script type="text/javascript" language="javascript">
        $("#CreateNewCategory").click(function() {
            $.getJSON("/category/create/",
                      { "CategoryName": $("#NewCategoryName").val() },
                      CategoryAdded);
                  });
    
                  function CategoryAdded(category) {
                      $("#CategoryList").append("<li><a href=\"" + category.URLName + "/cat\">" + category.Name + "</a></li>");
                  }
    </script>
  12. (When using IIS 6) Use httphandlers and httpmodules for http compression and client side static file caching
    The problem with using wildcard mapping under IIS6 is that you loose a lot of IIS functionality like http compression and client side static file caching.  I use this nifty work around: http://code.msdn.microsoft.com/fastmvc
  13. Cache Appropriate Actions
    It can be useful to cache the rendered action results of pages that do not change very often.

    public class HomeController : Controller
    {
        [OutputCache(Duration=86400, VaryByParam="none")]
        public ActionResult Index()
        {
  14. Restricting Post Data Binding / UpdateModel
    Be careful when using UpdateModel to bind posted form values to an object.  Let’s say that you want to allow a user to edit a product while restricting them from changing the price.  Even if you don’t include an input box on your form someone could fake a form post.  There are several ways to protect against this.

    One way is to give a property exclude list to the UpdateModel method.  Here are those pesky magic strings again.

    UpdateModel(prod, null, null, new[] { "Price" });

    I prefer the strongly typed method of defining an interface that only has the properties that should be bound.

    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Edit(int id, FormCollection form)
    {
        var prod = productRepository.GetProductByID(id);
        try
        {
            UpdateModel<Models.IProductEdit>(prod);
            productRepository.Save();
  15. Note on Html Helper Magic Strings
    Again I think that magic strings should be avoided, but the default Html Helpers are full of them.  One approach is to use constants to contain the strings to one spot.  Another approach that is available in MVC Futures uses lambda expressions.  You could have something like this.

    <%= Html.ActionLink<HomeController>(c => c.Index()) %>

    Instead of.

    <%= Html.ActionLink("Home", "Index", "Home") %>

    The only problem is that this approach uses compiled lambda expressions which can have performance/scaling issues.  It is my understanding that these issues have been fixed in ASP.NET MVC v2 (thanks for the update Elijah Manor).

 

Download

Keep in mind that my sample project is not 100% best practices.  I was trying to start simple then refactoring toward better practices.  You can download the sample below (the sample product pictures come from the Microsoft MVC StoreFront sample).

ASP.NET MVC Sample Download

 

Convert ROWS TO COLUMNS

ROWS TO COLUMNS

How to convert rows into columns? We’re going to see how to do that using query, and there is the “Pivot” function in SQL Server 2005.

The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

Using Query:

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:

Example: We have 2 tables: LKUP_Subject which is a lookup table that holds the subject names, and the Student_Subject which contains the student grades for the different subject.

We are going to build the query having fixed columns:

  1. We use the “if-else” functionality in the query to put them in the defined columns (in SQL, it is “case”):

SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject

2.       Then we use the “SUM” function to merge the results in 1 row like we want:

SELECT StudentId,
SUM(Physics) AS Physics,
SUM(Chemistry) As Chemistry,
SUM(Math) AS Math,
SUM(English) As English
FROM
(SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject) s
GROUP BY StudentId


 

Now, we will build it dynamically using cursor (you can do it using temporary tables to do the same functionality for performance reasons):

 

DECLARE Cur CURSOR FOR
SELECT DISTINCT id, ‘[‘ + Description_En + ‘]’ AS Description_En
FROM LKUP_Subject

DECLARE @SubjectName NVARCHAR(MAX),
@SubjectId INT,
@Sum NVARCHAR(MAX), — The SUM part of the query
@Select NVARCHAR(MAX), — The inner query
@Sql NVARCHAR(MAX) — The total sql statement

SET @Select = ”
SET @Sum = ”
SET @Sql = ”

OPEN Cur
FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sum = @Sum + ‘SUM(‘ + @SubjectName + ‘) AS ‘ + @SubjectName + ‘,’
SET @Select = @Select + ‘(CASE WHEN SubjectId = ‘ + CONVERT(NVARCHAR(10), @SubjectId) + ‘ THEN Grade END) AS ‘ + @SubjectName + ‘,’
FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName

END

CLOSE Cur
DEALLOCATE Cur
IF RIGHT(@Select, 1) = ‘,’
SET @Select = STUFF(@Select, LEN(@Select), 1, ‘ FROM Student_Subject’)

IF RIGHT(@Sum, 1) = ‘,’
SET @Sum = STUFF(@Sum, LEN(@Sum), 1, ”)

SET @Sql = ‘SELECT StudentId, ‘ + @Sum + ‘ FROM (SELECT StudentId, ‘ + @Select + ‘) s GROUP BY StudentId’

EXEC sp_executesql @Sql

Using Pivot:

In SQL Server 2005, there is a new feature that does all of this in a single step: PIVOT

In fixed columns, here is how we use it:

SELECT StudentId, Physics, Chemistry, Math, English
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(Physics, Chemistry, Math, English)) AS pvt

Note: that you should use an aggreate function like the SUM (for the same reason as you should use an aggreate function when using the query to transform Rows to Columns)

 

As for how to do it dynamically:

DECLARE Cur CURSOR FOR
SELECT DISTINCT Description_En
FROM LKUP_Subject

DECLARE @Temp NVARCHAR(MAX),
@AllSubjects NVARCHAR(MAX),
@SubjectQuery NVARCHAR(MAX)

SET @AllSubjects = ”

OPEN Cur
— Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AllSubjects = @AllSubjects + ‘[‘ + @Temp + ‘],’
FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllSubjects = SUBSTRING(@AllSubjects, 0, LEN(@AllSubjects))

— Building the pivot query
SET @SubjectQuery = ‘SELECT StudentId, ‘ + @AllSubjects + ‘
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(‘ + @AllSubjects + ‘)) AS pvt’

EXEC sp_executesql @SubjectQuery

Filed under: