Thursday, August 12, 2010

SQL SERVER: Creating Comma Separate Values List from Table Column

There are two ways to generate a comma separated list from a table column. First is to manually concatenate the result and the second that is more sophisticated is to use the built-in function COALESCE of SQL Server.

Example Problem:
For example you have a table named COUNTRY that has a column named NAME, and you want to generate a comma separated list for countries.

Solution 1:
This is the simple but not the smartest way to implement it:

SET @listStr = ''
SELECT @listStr = @listStr + ISNULL(NAME,'') + ','
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

Solution 2:
This is the smartest way:

SELECT @listStr = COALESCE(@listStr+',' , '') + NAME
SELECT @listStr

Monday, August 2, 2010

Javascript: Checkall/Uncheckall feature in the enclosing Form

on the web page dynamically generated, I am having different number fo forms and in each form I have a list of checkboxes genererated dynamically.
The number of textboxes in a form may varry, but in each form the checkbox Id/name will have a certain prefix to identify.
The following piece of Java Script code can be used to checkall/uncheckall the checkboxes in a specific form on some event(button, checkbox or any other javascript event)

Simply place the following piece of code in the head section of your web page:

*Prototypes for JS functions
String.prototype.startsWith = function(str){return (this.match("^"+str)==str)}
String.prototype.endsWith = function(str){return (this.match(str+"$")==str)}

*Check/Uncheck all the checkboxes in the selector column of a report.
function getArray(formName,inputtype, prefix)

var checkBoxesArray=new Array();
var count = 0;
for(i=0; i < formName.elements.length ; i++)
var elmNameStr = formName.elements[i].name.toString();
var elmName =formName.elements[i];

//alert (elmName+ " :: elementName (startsWith): "+ elmNameStr.startsWith(prefix));

if(formName.elements[i].type==inputtype && elmNameStr.startsWith(prefix))
checkBoxesArray[count++] = elmName;
//alert(checkBoxesArray.length + " checkboxes found");
return checkBoxesArray;

function checkall(elem,flag,pattren)
var form = findParentForm(elem);

var arrayCheckboxes = getArray(form,"checkbox", pattren);

for ( i = 0 ; i < arrayCheckboxes.length ; i++) {
arrayCheckboxes[i].checked = flag;

function findParentForm(elem){
var parent = elem.parentNode;
if(parent && parent.tagName != 'FORM'){
parent = findParentForm(parent);
return parent;


To call the checkall functionality, simply call "checkall" function as:

onclick="javascript:checkall(this, this.checked,'butt-');"

@param1: Element Id you are calling on the function, usually a checkbox
@param2: Current state of check box, whether the checkbox state is checked or unchecked. If this checkbox is checked then all the associated checkboxes should also be checkd and vice versa
@param3: String prefix for the checkbox Id/name pattern to identify the appropriate list.


<form name ="formName0">

check/uncheck all: <input type ="checkbox" name ="test" onclick="javascript:checkall(this, this.checked,'butt-');"/> <br/>
Must Change State: <br/>
<input type ="checkbox" name ="butt-1234"/>
<input type ="checkbox" name ="butt-2141"/>
<input type ="checkbox" name ="butt-3_abh"/>
<input type ="checkbox" name ="butt-asgas"/>
<input type ="checkbox" name ="butt-0"/>
Must have ignored:<br/>
<input type ="checkbox" name ="abc"/>
<input type ="checkbox" name ="def"/>

<h3> Form 2 </h3>
<form name ="FormName1">
<div id ="asda" name ="divName">
check/uncheck all: <input type ="checkbox" name ="test" onclick="javascript:checkall(this, this.checked,'selected-');"/> <br/>
Must Change State: <br/>
<input type ="checkbox" name ="selected-1af"/>
<input type ="checkbox" name ="selected-wf2"/>
<input type ="checkbox" name ="selected-3_abh"/>
<input type ="checkbox" name ="selected-asgas"/>
<input type ="checkbox" name ="selected-0"/>
Must have ignored:<br/>
<input type ="checkbox" name ="selected_sabah"/>
<input type ="checkbox" name ="abnsd"/>