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