How to Get JSON of each row of the Table-CodePudding

I am facing an issue getting the JSON of each row. I have the table which contains OrderId and TotalCost. I need to get the table data for OrderID and TotalCost with JsonData.

Can someone please help me with this, How i can write the query which give me the json data of each row of the table.

  CREATE TABLE tblOrder ( [Orderid] int, [TotalCost] decimal(18,2) )
GO

INSERT INTO tblOrder ([Orderid], [TotalCost])
VALUES
( 1, 1770.00 ), 
( 2, 200.00 ), 
( 3, 100.00 ), 
( 4, 200.00 ), 
( 5, 50.00 )
Go
SELECT * FROM tblOrder

The is the Table I have which contains below Data

and the desire output I need is

CodePudding user response：

It can be as simple as this:

SELECT * 
      ,JSONData =  (Select A.* For JSON Path,Without_Array_Wrapper )
 FROM tblOrder A

Note the alias A and A.*

Results

CodePudding user response：

If you need the other columns in the result, and not just the JSON you can use this

DECLARE @tblOrder TABLE ([Orderid] INT,
                         [TotalCost] DECIMAL(18, 2));

INSERT INTO @tblOrder ([Orderid], [TotalCost])
VALUES (1, 1770.00),
       (2, 200.00),
       (3, 100.00),
       (4, 200.00),
       (5, 50.00);

SELECT Orderid,
       TotalCost,
       (SELECT Orderid, TotalCost
        FROM @tblOrder
        WHERE Orderid = t.Orderid
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) JsonData
FROM @tblOrder t;

If you just need the JsonData column it's simpler:

SELECT Orderid, TotalCost
FROM tblOrder
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;