List items of each order in one row in SQL-CodePudding

I have a table of orders. I want to obtain table 2 from table 1.

Table1:

OrderID	ItemId
1	1
1	2
2	3

Table2:

OrderID	Items
1	[{"ItemId":1},{"ItemId":2}]
2	[{"ItemId":3}]

How can I obtain table 2?

CodePudding user response：

If you're using SQL Server 2017 or above, there is STRING_AGG function that will do exactly this for you:

SELECT OrderId, '['   STRING_AGG('{"ItemId":'   ItemId   "}, ',')   ']' AS ItemId FROM Table1;

However, looking at the concatenated value, it looks like you're trying to construct JSON output in your SQL query. If that is the case, you'd better be off doing this in the application layer, or using FOR JSON construct as explained here.

CodePudding user response：

SELECT 
  T1.[OrderID],
  (SELECT T2.ItemId FROM OrderTable T2 WHERE T1.OrderID=T2.OrderID FOR JSON PATH) AS 'Items'
  FROM OrderTable  AS T1
  GROUP BY T1.[ArchetypeID]