Common Table Expression (CTE) in SQL Server 2008 R2 and get Column value split on comma as row

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs—recursive and non-recursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008.

State Table

CREATE TABLE [dbo].[tb_State](

[SID] [nchar](40) NOT NULL,

[Statename] [nvarchar](200) NULL

) ON [PRIMARY]

City Table

CREATE TABLE [dbo].[tb_City](

[CID] [nchar](40) NOT NULL,

[Statename] [nvarchar](200) NULL,

[Cityname] [nvarchar](500) NULL

) ON [PRIMARY]

Working with Common Table Expressions

You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:

[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

Example of Common Table Expressions:

WITH cte_BRIJESH AS

(

SELECT a.CID,a.Statename,b.SID,SUBSTRING(',' + a.Cityname + ',', Number + 1,

CHARINDEX(',', ',' + a.Cityname + ',', Number + 1) - Number -1)AS CityName

FROM master..spt_values

cross join dbo.tb_City a

 JOIN tb_State b ON a.Statename = b.Statename

where type = 'P'

and Number <= LEN(',' + a.Cityname + ',') - 1

AND SUBSTRING(',' + a.Cityname + ',', Number, 1) = ','

)

SELECT * FROM cte_BRIJESH

Result:

Get multiple values from checkboxlist selected values in textbox as comma seperated using javascript in asp.net

Java Script Funtion:

function GetSelectedValue() {
        var chkBox = document.getElementById("<%=chkList.ClientID%>");
        var checkbox = chkBox.getElementsByTagName("input");
        var objTextBox = document.getElementById("<%=txtChkValue.ClientID%>");
        var counter = 0;
        objTextBox.value = "";
        for (var i = 0; i < checkbox.length; i++) {
            if (checkbox[i].checked) {
                var chkBoxText = checkbox[i].parentNode.getElementsByTagName('label');
                if (objTextBox.value == "") {
                    objTextBox.value = chkBoxText[0].innerHTML;
                }
                else {
                    objTextBox.value = objTextBox.value + ", " + chkBoxText[0].innerHTML;
                }
            }
        }

    }

where chkList is CheckboxList and txtChkValue is Textbox as given below:

<asp:TextBox ID="txtChkValue1" runat="server" CssClass="textbox"
                Width="270px" Height="25px"></asp:TextBox>
             
                <asp:CheckBoxList ID="chkList1" runat="server" onclick="GetSelectedValue1();">
                </asp:CheckBoxList>

Query to remove last comma/character in column update in sql server

Try this sql query to update data of column as remove last comma from column value..........

" UPDATE tablename
SET colname= LEFT(colname, len(colname) -1)
WHERE RIGHT(colname,1) = ',' "