Let us say we have the below table
DECLARE @T TABLE(SlNo INT IDENTITY,Name VARCHAR(10),MonthName VARCHAR(20))
INSERT INTO @T VALUES
('Test Name1','SEP 2014'),('Test Name2','MAY 2016')
The relational query result will be
SELECT
SlNo = ROW_NUMBER() OVER(ORDER BY YEAR(MonthName) DESC, MONTH(MonthName))
,Name
,MonthName
FROM @T
/*
SlNo Name MonthName
1 Test Name2 MAY 2016
2 Test Name1 SEP 2014
*/
SQL Server 2016 has introduce
FOR JSON AUTO by using which we can transform the relational result into JSON result as under
DECLARE @T TABLE(SlNo INT IDENTITY,Name VARCHAR(10),MonthName VARCHAR(20))
INSERT INTO @T VALUES
('Test Name1','SEP 2014'),('Test Name2','MAY 2016')
SELECT
SlNo = ROW_NUMBER() OVER(ORDER BY YEAR(MonthName) DESC, MONTH(MonthName))
,Name
,MonthName
FROM @T
FOR JSON AUTO
/*
[
{
"SlNo": 1,
"Name": "Test Name2",
"MonthName": "MAY 2016"
},
{
"SlNo": 2,
"Name": "Test Name1",
"MonthName": "SEP 2014"
}
]
*/