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.
x
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]);