Demonstration of SQL Server 2016 JSON Auto

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 194
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"
}
]

*/

Comments or Responses

Login to post response