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:

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + ISNULL(NAME,'') + ','
FROM COUNTRIES
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

Solution 2:
This is the smartest way:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + NAME
FROM COUNTRIES
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)}

/**
*START SEGMENT:
*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;
}

/**
*END SEGMENT:
*/


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.

Example:



<form name ="formName0">

check/uncheck all: <input type ="checkbox" name ="test" onclick="javascript:checkall(this, this.checked,'butt-');"/> <br/>
<hr/>
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"/>
<hr/>
Must have ignored:<br/>
<input type ="checkbox" name ="abc"/>
<input type ="checkbox" name ="def"/>

</form>
<hr/>
<h3> Form 2 </h3>
<form name ="FormName1">
<span>
<div id ="asda" name ="divName">
check/uncheck all: <input type ="checkbox" name ="test" onclick="javascript:checkall(this, this.checked,'selected-');"/> <br/>
<hr/>
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"/>
<hr/>
Must have ignored:<br/>
<input type ="checkbox" name ="selected_sabah"/>
<input type ="checkbox" name ="abnsd"/>

</div>
</span>
</form>