Split using CTE

Create function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimeter nvarchar (10)
)
returns @RetTable table (pos int IDENTITY(1,1), String nvarchar(4000))
Begin
set @String = @String + @Delimeter;

with MySplit(x,y,z,Result)
as
(
select x = @String, y= @Delimeter , z = 0, Result = @String
union all
select ltrim(substring(x,charindex(y,x)+len(y),len(x))),y,z+charindex(y,x),substring(x,0,charindex(y,x)) from MySplit where len(x) >= len(y) and len(Result) >0
)

insert into @RetTable select Result from MySplit where z>0

return
End


--more than 100 entries are not supported here, so if the result is more than 100 rows then it will geve error

Split in SQLServer

Create function [dbo].[fn_Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table (pos int,[Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(2)
declare @index int
declare @lenstring int

set @index=1
set @lenstring= len(@Delimiter)
set @NextString = ''
set @CommaCheck = right(@String,@lenstring)
set @String = @String + @Delimiter

set @Pos = charindex(@Delimiter,@String)
set @NextPos =@lenstring
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)
insert into @ValueTable (pos, [Value]) Values (@index,@NextString)
set @String = substring(@String,@pos +@lenstring,len(@String))
set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
set @index = @index +1
end

return
end

Fibonacci series using CTE

with MyCTE(x, y)
as
(
select x = 1 , y = 1
union all
select x + y,x from MyCTE where x<100
)
select x from MyCTE
order by x

PIVOT Queries

Pivot is basically used for the purpose of Presentation of data in the reports, and by using PIVOT the case and group by clauses are no longer useful

Following is an example of PIVOT table for getting the SLA for the different application

create a table
CREATE TABLE [dbo].[WeeklyStatus](
[Ticket] [int] NOT NULL,
[SLA] [int] NULL,
[Module] [nvarchar](50) )make Ticket and Module pair as primary key

Insert few elements in the table and run the following query to get the no of tickets meet SLA for the application

select [Module],
[0] as SLAnotMeet,
[1] as SLAMeet
from (select [Module],[SLA],[Ticket] from WeeklyStatus) s
PIVOT
( count([Ticket]) for [SLA] in ([0],[1])) ws
order by [Module]

Update list item using web service

WS.Lists lst = new WS.Lists();
lst.Credentials = new NetworkCredential("userName", "Password","domain");
lst.Url = @"SiteURL/_vti_bin/Lists.asmx";

XmlDocument doc = new XmlDocument();
XmlElement element = doc.CreateElement("Batch");

string item = "<Method ID='1' Cmd='New'>" +
"<Field Name='Title'>mritunjay</Field>" +
"<Field Name='Area'>" + getID(lst, "11", "Area") + "</Field>" +
"<Field Name='Gasket'>None</Field>" +
"</Method>";
element.InnerXml = item;
lst.UpdateListItems("ListName", element);