Skip to content
Advertisement

How can I generate JSON results in older SQL Server versions?

I have a table which contains data similar to this. I want to get ItemNames in JSON format as I have mentioned in the expected output below.

OrderId         CustomerId    ItemId        ItemName
-------         ----------    ------        ---------
 1                   14          6            Apples
 2                   14          7            Oranges
 3                   23         10            Mangoes

Expected output:

 ResultId      CustomerId           OrderedItems
 --------      ----------           ---------------------------------------
    1              14              [{"ItemId":6,"ItemName":"Apples"},{"ItemId":7,"ItemName":"Oranges"}]
    2              23              [{"ItemId":10,"ItemName":"Mangoes"}] 

Note: I know in latest SQL Server, there are functions FOR JSON to convert to JSON format but it won’t work in older SQL versions. Therefore, I am expecting the answer which should work on previous versions as well.

Advertisement

Answer

Something based on this?

DECLARE @DataSource TABLE
(
    [OrderID] INT
   ,[CustomerID] INT
   ,[ItemID] INT
   ,[ItemName] NVARCHAR(128)
);

INSERT INTO @DataSource ([OrderID], [CustomerID], [ItemID], [ItemName])
VALUES (1, 14, 6, 'Apples')
      ,(2, 14, 7, 'Oranges')
      ,(3, 23, 10, 'Mangoes');

WITH DataSource AS
(
    SELECT DISTINCT [CustomerID]
    FROM @DataSource
)
SELECT ROW_NUMBER() OVER (ORDER BY DS.[CustomerID]) AS ResultId      
      ,*
FROM DataSource DS
CROSS APPLY
(
    SELECT '[' + STUFF
    (
        (
            SELECT ',' + CONCAT('{"ItemId":', [ItemID],',"ItemName":"', [ItemName],'"}')
            FROM @DataSource
            WHERE [CustomerID] = DS.[CustomerID]
            ORDER BY [ItemID]
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)')
        ,1
        ,1
        ,''
    ) + ']'
) Items ([OrderedItems]);

enter image description here

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement