SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create the Items table
CREATE TABLE Items (
    ItemID INT PRIMARY KEY,
    ItemName NVARCHAR(50),
    ItemCategory NVARCHAR(50),
    ItemDescription NVARCHAR(100)
);

-- Insert sample data into the Items table
INSERT INTO Items (ItemID, ItemName, ItemCategory, ItemDescription)
VALUES
    (1, 'Item A', 'Category 1', 'Description for A'),
    (4, 'Item D', 'Category 2', 'Description for D'),
    (3, 'Item C', 'Category 2', 'Description for C'),
    (2, 'Item B', 'Category 1', 'Description for B'),
    (5, 'Item E', 'Category 1', 'Description for E');
5 rows affected

SELECT STRING_AGG(ItemName, ', ') AS ItemNameListAsc
FROM dbo.Items;


ItemNameListAsc


Item A, Item B, Item C, Item D, Item E


SELECT STRING_AGG(ItemName, ', ')WITHIN GROUP(ORDER BY ItemName DESC) AS ItemNameListDesc
FROM dbo.Items;


ItemNameListDesc


Item E, Item D, Item C, Item B, Item A


SELECT STRING_AGG(CONCAT_WS('|', ItemID, ItemName, ItemDescription), ', ')WITHIN GROUP(ORDER BY ItemName) AS ConcatItemDetails
FROM dbo.Items;


ConcatItemDetails


1|Item A|Description for A, 2|Item B|Description for B, 3|Item C|Description for C, 4|Item D|Description for D, 5|Item E|Description for E


SELECT ItemCategory,
       STRING_AGG(ItemName, ', ') AS ItemList
FROM dbo.Items
GROUP BY ItemCategory;


ItemCategory
ItemList


Category 1
Item A, Item B, Item E

Category 2
Item C, Item D


SELECT ItemCategory,
       STRING_AGG(ItemName, ', ')WITHIN GROUP(ORDER BY ItemName DESC) AS ItemListSorted
FROM dbo.Items
GROUP BY ItemCategory;


ItemCategory
ItemListSorted


Category 1
Item E, Item B, Item A

Category 2
Item D, Item C


SELECT ItemCategory,
       STRING_AGG(CONCAT_WS('|', ItemID, ItemName, ItemDescription), ', ')WITHIN GROUP(ORDER BY ItemName) AS ItemListSorted
FROM dbo.Items
GROUP BY ItemCategory;


ItemCategory
ItemListSorted


Category 1
1|Item A|Description for A, 2|Item B|Description for B, 5|Item E|Description for E

Category 2
3|Item C|Description for C, 4|Item D|Description for D

ItemCategory	ItemListSorted
Category 1	1\|Item A\|Description for A, 2\|Item B\|Description for B, 5\|Item E\|Description for E
Category 2	3\|Item C\|Description for C, 4\|Item D\|Description for D