Pivot in Sql

CREATE TABLE #tblitems(
  class    nVARCHAR(100),
  Subject  nVARCHAR(100),
  Total    INT
)

INSERT INTO #tblitems
SELECT 'Standard -12', 'Chemistry', 20 UNION ALL
SELECT 'Standard -11', 'Maths', 45 UNION ALL
SELECT 'Standard -12', 'Maths', 0 UNION ALL
SELECT 'Standard -11', 'Chemistry', 5 UNION ALL
SELECT 'Standard -12', 'Physics', 30 UNION ALL
SELECT 'Standard -11', 'Physics', 45 UNION ALL
SELECT 'Standard -11', 'Biology', 50 UNION ALL
SELECT 'Standard -12', 'Biology', 94

-- Creating a list of all distinct row values
-- that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                    SELECT DISTINCT ',[' + Subject + ']'
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT class, ' + @paramList
+ ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(Total) FOR Subject IN (' + @paramList + ')) pvt order by class desc'

EXEC sp_executesql @query
DROP TABLE #tblitems

Find Label inside Templete column of GridView Javascript

Hi, An Easy way to find Label inside Gridview through Javascript.
Label control actually renfered like Span the Html  Control. you can see by Using Firebug

In GridView

   <asp:TemplateField HeaderText="Employee Name" HeaderStyle-HorizontalAlign="Left" HeaderStyle-Width="110px">
                   
                        <ItemTemplate>
                            <asp:Label ID="lblDisplayEmp" runat="server" Text='<%# Eval("DisplayEmpName") %>'></asp:Label>       
                            <asp:Label ID="hidEmpname" style="display:none;"     runat="server" Text='<%# Eval("EmpName") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>


  var grd = document.getElementById("<%= GridView1.ClientID %>");
  for (i = 1; i < grd.rows.length; i++) {
var empName = grd.rows[i].cells[17].getElementsByTagName("span");
                alert(empName[1].innerHTML);
}

Note :  empName[1].innerHTML- it will give you first label
             empName[0].innerHTML  it will give you Second label

get distinct values from string

An easy way to remove duplicate from string and get distinct values


 var input = '1,2,3,1,4,6,7,2,3';
                var splitted = input.split(',');
                var collector = {};
                for (i = 0; i < splitted.length; i++) {
                    key = splitted[i].replace(/^\s*/, "").replace(/\s*$/, "");
                    collector[key] = true;
                }
                var out = [];
                for (var key in collector) {
                    out.push(key);
                }
                var output = out.join(',');

OutPut will be 1,2,3,4,6,7

Set Fix Header to Ajax AutoCompleteExtender

Ajax Auto complete onclient showing ,Yuo can set a fix header


AutoComplete Extender Properties to be Set
OnClientShowing="AutoCompleteHeader" BehaviorID="Behav1"



Javascript:-

function AutoCompleteHeader()
        {    
     
        var extender = $find("Behav1");  
        var message = "Name|Class|RollNo";
    jQuery(extender.get_completionList()).prepend("<li rel='header' style='background-color:#0263B1 !important;font-weight: bold;color:white;cursor : Not-allow !important;'>" + message + "</li>");
 
        }

Note EmployeeAutoCompleteEx7- is BehaviourId of AutoComplete

Get and Set WaterMark Text of AJAX

Hii  ,I will tell You how can we get Watermark text of Ajax Tool through Javascript
This Can be done by setting Behaviour Id of Watermark Extneder


<asp:TextBox runat="server" ID="txtproId" CssClass="invisible"  />
                                                <cc1:TextBoxWatermarkExtender ID="wtmProjectName" runat="server" BehaviorID="projCatbehav" TargetControlID="txtProjectCategory"
                                                    WatermarkCssClass="watermarked" WatermarkText="Goldy" />

function validate() {

}

To get the Text 

var procat11= $find("projCatbehav").get_Text();

Note:-projCatbehav is an Behaviour Id

To Set the Text

$find("projCatbehav").set_Text("Please Type Here");