Convert select query results into Json

For SQL server 2017

CREATE TABLE mytable(
  ID int PRIMARY KEY,
  Name varchar(50),
  teamName varchar(255),
  Create_Date DATETIME
);

INSERT INTO mytable VALUES (1,NULL,'TEST1','2017-01-02');
INSERT INTO mytable VALUES (2,NULL,'TEST2',NULL);
INSERT INTO mytable VALUES (3,'KK','TEST3','2017-01-02');
INSERT INTO mytable VALUES (4,NULL,NULL,NULL);

Try below way here i provide an example

SELECT 
       ID,  
       Name,  
       teamName,  
       Create_Date 
   FROM mytable 
   FOR JSON AUTO  

http://www.sqlfiddle.com/#!18/81350/1

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{"ID":1,"teamName":"TEST1","Create_Date":"2017-01-02T00:00:00"},{"ID":2,"teamName":"TEST2"},{"ID":3,"Name":"KK","teamName":"TEST3","Create_Date":"2017-01-02T00:00:00"},{"ID":4}]

For below vesion of sql 2017 server:

1st create a scaler function

create FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
    Declare @Head varchar(max) = '',@JSON varchar(max) = ''
    ; with cteEAV as (Select RowNr=Row_Number() over (Order By (Select NULL))
                            ,Entity    = xRow.value('@*[1]','varchar(100)')
                            ,Attribute = xAtt.value('local-name(.)','varchar(100)')
                            ,Value     = xAtt.value('.','varchar(max)') 
                       From  @XML.nodes('/row') As R(xRow) 
                       Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
          ,cteSum as (Select Records=count(Distinct Entity)
                            ,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"results":[[getResults]]}') ) 
                       From  cteEAV)
          ,cteBld as (Select *
                            ,NewRow=IIF(Lag(Entity,1)  over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
                            ,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
                            ,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"') 
                       From  cteEAV )
    Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
    Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
End
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)

Then use this function json conversion below query is an example

Declare @Table table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @Table values
(1,1,'John','Smith','[email protected]'),
(2,0,'Jane','Doe'  ,'[email protected]')

Select A.ID
      ,A.Last_Name
      ,A.First_Name
      ,B.JSON
From  @Table A 
Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,1,(Select A.* For XML Raw)) ) B

From SQL-Server 2016+, you can use JSON AUTO to return Json:

Select * From Customers FOR JSON AUTO;

If you expect to return just a single row, then you can add Without_Array_Wrapper after FOR JSON AUTO with a comma after the AUTO keyword, and you will get an object instead of an array:

Select * From Customers FOR JSON AUTO, Without_Array_Wrapper;

Note: If you add the Without_Array_Wrapper and return multiple rows, you will get a newly populated object type but won't get any error - which requires carefulness as you won't know if anything is wrong and might hit you later when exercising the data.

JSON AUTO would make quick work of this in but JSON support is available only in SQL Server 2016 and later, including Azure SQL Database. For a T-SQL solution in SQL 2014 and earlier, you'll need to build the JSON string yourself.

Below is an example that uses a FOR XML subquery to concatenate the result into JSON format and adds the outermost [ and ] for the array. Note that this does not handle characters that must be escaped in JSON (\ and ") so you'll need to use REPLACE to escape those if contained in your data.

SELECT '[' + STUFF((
    SELECT
          ',{'
        + '"Id":'+CAST(Id AS varchar(10)) + ','
        + COALESCE('"Name":"' + Name + '",','')
        + COALESCE('"About":"' + About + '",','')
        + COALESCE('"Age":'+CAST(Age AS varchar(10)) + ',','')
        + COALESCE('"AddressId":'+CAST(AddressId AS varchar(10)), '')
        + '}'
    FROM TestTable
    FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')
    + ']';