Recursive CTE in SQL SERVER

This shows the use of recursive cte that displays a next six month based on passing the values (Year and Month).


DECLARE @year int,
@month int

SELECT @year = 2010,
@month = 9

;WITH cte AS (
SELECT
DATEADD(MONTH,@month-1,DATEADD(year,@year-1,cast('00010101' as date))) as date1
UNION ALL
SELECT DATEADD(month,1,date1)
FROM cte
WHERE date1 < DATEADD(MONTH,@month + 4,DATEADD(year,@year-1,cast('00010101' as date)))
)

SELECT * FROM cte
GO

And Output will be :

date1
-------------
2010-09-01
2010-10-01
2010-11-01
2010-12-01
2011-01-01
2011-02-01

UNPIVOT in SQL SERVER with Example

Here in this example it shows the Max column value for the specific ID
how it is get by UnPIVOT let see :

DECLARE @Test AS TABLE(id INT, col1 INT, col2 INT, col3 INT, col4 INT)
INSERT INTO @Test VALUES(1, 100, 500, 300, 200)
INSERT INTO @Test VALUES(2, 600, 300, 500, 400)
INSERT INTO @Test VALUES(3, 400, 100, 800, 300)

SELECT ID,MAX(data) AS MaxValue
FROM
(
SELECT ID,Data
FROM
(SELECT ID, col1, col2, col3, col4
FROM @Test
) p
UNPIVOT
(Data FOR Datavalue IN
(col1, col2, col3, col4)
)AS unpvt
) AS x
GROUP BY ID
GO

The output will be :
-------------
ID MaxValue
----- ------------
1 500
2 600
3 800

Count the difference of character in two string in SQL Server

Create the sql function and use it like below :


--Select dbo.DiffCount('BOB', 'BOB')
CREATE FUNCTION dbo.DiffCount(@Str1 varchar(500), @Str2 varchar(500))
Returns int As
BEGIN
DECLARE @len1 INT,
@len2 INT,
@DiffLen INT,
@CharIndex INT,
@Inc INT,
@diffcount INT

SELECT @len1 = DATALENGTH(@str1),
@len2 = DATALENGTH(@str2),
@DiffLen = @len1 - @len2,
@diffcount = 0,
@Inc = 1

IF @len1 <= @len2 BEGIN

WHILE @Inc <= @len2 BEGIN

IF SUBSTRING(@str1,@inc,1) = NULLIF(SUBSTRING(@str2,@inc,1),' ') BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END
END ELSE IF @len1 > @len2 BEGIN
WHILE @Inc <= @len1 BEGIN

IF NULLIF(SUBSTRING(@str1,@inc,1),' ') = SUBSTRING(@str2,@inc,1) BEGIN
set @diffcount = @diffcount
END ELSE BEGIN
SET @diffcount = @diffcount + 1
END
SET @Inc = @Inc + 1
END

END
RETURN @diffcount
END

Retrieve comma separated values into a table

Hi ,

You can use this table vale function to get the comma separated string into form of table retrieve value.

just create this function and select result from that .

--SELECT * from dbo.CommaSeparatedToTableVale('app,birds,cow,paresh,dhaval')
CREATE FUNCTION CommaSeparatedToTableVale
(
@str VARCHAR(100)
)
RETURNS @Table
TABLE(rec VARCHAR(50))
AS
BEGIN
DECLARE @len INT
DECLARE @inc int
DECLARE @offset INT
DECLARE @newstr VARCHAR(100)

select @len = DATALENGTH(@str),
@inc = 0,
@offset = 0

while @inc <= @len+1 begin

IF ISNULL(@newstr,'') = '' BEGIN
SELECT @offset = charindex(',',@str)
INSERT INTO @Table
SELECT SUBSTRING(@str,0,@offset)
SELECT @newstr = SUBSTRING(@str,@offset+1,@len)
END ELSE BEGIN
SELECT @offset = charindex(',',@newstr)
if @offset = 0 begin
INSERT INTO @Table
SELECT @newstr
break
end else begin
INSERT INTO @Table
SELECT SUBSTRING(@newstr,0,@offset)
end
SELECT @newstr = SUBSTRING(@newstr,@offset+1,@len)
END
SET @len = @len - @offset
SET @inc = @inc + 1
end
--RETURN @table
return
END

And you may also refer this for using XML :

http://sqlyoga.com/2009/05/sql-server-get-comma-separated-values.html