How to generate string json formate to this case ?

Question

I need to make json string formate suitable to select statement as following : select FooterTable.LineNumber,FooterTable.ItemCode,FooterTable.Quantity,FooterTable.UniPrice,FooterTable.Total from MasterTable inner join FooterTable on MasterTable.Serial=FooterTable.Serial,MasterTable.BranchCode=FooterTable.BranchCode,MasterTable.Year=FooterTable.Year where MasterTable.Serial=10 AND MasterTable.Year=2019 AND MasterTable.BranchCode=1 No need for code by any language only string fomate like that { xxxx } MasterTable Serial pk Year pk BranchCode pk FooterTable Serial pk Year pk BranchCode pk LineNumber ItemCode Quantity UniPrice Total How to generate json string as following : Fields : LineNumber,ItemCode,Quantity,UniPrice,Total specified all fields from FooterTable no fields from MasterTable . Keys : Serial:10,Year:2019,BranchCode:1 from MasterTable . Tables:MasterTable,FooterTable . so that how to generate json format string to my requirment above

Jaimin Shethiya · Answer

Hello ahmed, You have following statement write after your select statement. "for json path" or "for json auto" If you use for json path then they have to json build based your query. if you use for json auto then they have to json build based on your joins. Example: "select FooterTable.LineNumber,FooterTable.ItemCode,FooterTable.Quantity,FooterTable.UniPrice,FooterTable.Total from MasterTable inner join FooterTable on MasterTable.Serial=FooterTable.Serial,MasterTable.BranchCode=FooterTable.BranchCode,MasterTable.Year=FooterTable.Year where MasterTable.Serial=10 AND MasterTable.Year=2019 AND MasterTable.BranchCode=1 for json path" Note: if your system SQL Server 2016 or later then it will support otherwise not support. let me know if you needed anything. Thanks