JSON, Please help creating the Json format from a table

PC238 0 Reputation points

Hello Everyone,

In SQL Server 2019 Database, I have a table with columns StudentId, Agreement, StudentName

Please help me with the query which creates below Jason for each record.

{   "SingleValues": [    {     "fieldValue": "No",     "fieldName": "Agreement"    
						 {     "fieldValue": "John Doe",     "fieldName": "StudentName"    
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,813 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 11,796 Reputation points Microsoft Vendor

    Hi @PC238

    How about this query?

    CREATE TABLE #TEMP(StudentId INT, Agreement VARCHAR(20), StudentName VARCHAR(20))
    (1,'No','John Doe')
    SELECT *,CONCAT('{ "SingleValues": [ { "fieldValue": "'
    			   ,'", "fieldName": "Agreement" },{ "fieldValue": "'
    			   ,'", "fieldName": "StudentName" } ] }') AS [JSON]
    FROM #TEMP ;


    User's image

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments