JSON, Please help creating the Json format from a table

PC238 20 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.
11,548 questions
{count} votes

1 answer

Sort by: Most helpful
  1. CosmogHong-MSFT 16,851 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