JSON column

Question

Table

EMPID FName LName Title
1 A Z VP
2 B H Director

select Empid,FName,LName,Title from Table
FOR JSON PATH, ROOT('Employee')

My result will be
"Employee" :
[
{"Empid":"1",
"FName":"A",
"LName":"Z",
"Title":"VP"
},

{"Empid":"1",
 "FName":"B",
 "LName":"H",
 "Title":"Director"
}

]

Is it possbile get rows like this

Json Data EMPID FName LName Title

"Employee" : 1 A Z VP
[
{"Empid":"1",
"FName":"A",
"LName":"Z",
"Title":"VP"
}]

"Employee" : 2 B H Director
[
{"Empid":"2",
"FName":"B",
"LName":"H",
"Title":"Director"
}]

Answer 1

Check this query:

select  
    ( select Empid, FName, LName, Title from Table where EMPID=t.EMPID for json path, root('Employee') ) as [Json Data],
    EMPID, FName, LName, Title
from Table t