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