Merge several columns of JSON data and display as single column in jqGrid

Question

I want to merge some of the data (3 address columns to 1 column) I am retrieving via JSONP from Fusion Tables into jqGrid.

Does anyone know if this is possible/how to go about it? Unfortunately Fusion Tables SQL API does not currently support CONCAT via SELECT commands.

Oleg provided code for basically colspan-ing 2 columns if one had long data, but I actually want to take the data from several columns and present it as just one column in jqGrid

Thanks in advance

edit, added a snippet of code:

datatype: "jsonp", // "json" or "jsonp"
colNames: ["id","lat","long","Name","Address","","","Postcode"],
colModel:[
    {name:'id',index:'id',key:true,sorttype:'int',hidden:true,sortable:true},
    {name:'latitude',index:'latitude',hidden:true},
    {name:'longitude',index:'longitude',hidden:true},
    {name:'name',index:'name',width:170,sortable:false,sorttype:'text'},
    {name:'address_line_1',index:'address_line_1',width:400,formatter:function (cellvalue, options, rowObject) {
        addPart1 = rowObject[4];
        addPart2 = rowObject[5];
        addPart3 = rowObject[6];
        fullAddress = addPart1 + addPart2 + addPart3;
        return fullAddress;},sortable:false,sorttype:'text'},
    {name:'address_line_2',index:'address_line_2',sortable:false,sorttype:'text',hidden:true},
    {name:'address_line_3',index:'address_line_3',sortable:false,sorttype:'text',hidden:true},
    {name:'postcode',label:'postcode',width:80,sortable:false,sorttype:'text'}      
],
jsonReader: {
    cell: "", // the same as cell: function (obj) { return obj; }
    root: "table.rows",
    page: function (obj) { return 1; },
    total: function (obj) { return 1; },
    records: function (obj) { return obj.table.rows.length; }
},

Here's a generic public data example from a .gov table (my table is basically the same setup). I'll tidy up the question later so people can easily see the question/answer :)

<script type="text/javascript"> 
var queryText = "SELECT * FROM 185189";
jQuery(document).ready(function() {
    jQuery("#rTable").jqGrid({
        url: 'http://www.google.com/fusiontables/api/query?sql=' +
              encodeURI(queryText) + '&jsonCallback=?',
        postData: "",
        datatype: "jsonp",
        colNames: ["col1","col2","col3","col4"],
        colModel:[
            {name:'FACID',index:'FACID',key:true,sorttype:'int',sortable:true},
            {name:'FACNAME',index:'FACNAME'},
            {name:'FAC_ADDRESS1',index:'FAC_ADDRESS1',sortable:false,sorttype:'text'},
            {name:'FAC_ADDRESS2',index:'FAC_ADDRESS2',sortable:false,sorttype:'text'}
        ],
        jsonReader: {
            cell: "",
            root: "table.rows",
            page: function (obj) { return 1; },
            total: function (obj) { return 1; },
            records: function (obj) { return obj.table.rows.length; }
        },
        rowNum:10,
        rowList:[10,20,30],
        pager: '#pager2',
        sortname: 'name',
        sortorder: "asc",
        viewrecords: true,
        loadonce: true,
        height: "100%",
        multiselect: true,
        caption: ""
    }); // END CREATE GRID
    jQuery("#rTable").jqGrid('navGrid','#pager2',{edit:false,add:false,del:false}); // paging options
});
</script>

Answer 1

You can use custom formatter to construct column contain based on any input data for the row. The rowObject parameter represent the row of data returned from the server. The string returned by the custom formatter is the text or HTML text with will be displayed in the cell.

If you would have implementation problem you should post the URL with the fusion table which you use.

UPDATED: You can solve the problem of composed columns in different ways. The first one work with old version of jqGrid and is just rewriting of the formatter function to the following:

formatter: function (cellvalue, options, rowObject) {
    var rowObject = arguments[2];
    if ($.isArray(rowObject)) {
        return rowObject[4] + rowObject[5] + rowObject[6];
    } else {
        return rowObject.address_line_1 +
            rowObject.address_line_2 +
            rowObject.address_line_3;
    }
}

The small disadvantage of the way is that you will have unneeded hidden columns address_line_2 and address_line_3 which you will not really use.

More elegant solution will be to use new beforeProcessing callback function (event) (see my original suggestion to the feature here). The function will be called only in case of loading the data from the server. It allows you to modify the data returned from the server before the data will be processed by jqGrid. In the case you can event use default jsonReader:

colNames: ["lat", "long", "Name", "Address", "Postcode"],
colModel:[
    {name: 'latitude', hidden: true},
    {name: 'longitude', hidden: true},
    {name: 'name', width: 170},
    {name: 'address_line', width: 400},
    {name: 'postcode', width: 80}
],
cmTemplate: { sortable: false },
beforeProcessing: function (data) {
    var rows = data.table.rows, length = rows.length, i = 0, row;
    data.page = 1;
    data.total = 1;
    data.records = length;
    data.rows = [];
    for (; i < length; i += 1) {
        row = rows[i];
        data.rows.push({
            id: row[0],
            cell: [row[1], row[2], row[3], row[4] + row[5] + row[6], row[7]]
        });
    }
    delete data.table;
}

I don't has your original JSON data and don't tested the code above, but the code shows how you can construct new data based on the original data returned from the server.