在带有GROUP by的For XML Path中使用之前按顺序排序

Question

CREATE TABLE #AD 
(
[PART NUMBER] nvarchar(255), 
[PLANT] nvarchar(255), 
[VENDOR CODE] nvarchar(255), 
[SUPPLIER NAME] nvarchar(255), 
[POSTING DATE] int
)
INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
('123', '1001', '2', 'VEND2', 20220611)
SELECT 
AD.*,
REPLACE(STUFF((SELECT (', (' + AD2.[VENDOR CODE] + ') ' + AD2.[SUPPLIER NAME])
FROM #AD AD2
WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
AND AD.PLANT = AD2.PLANT 
AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
GROUP BY AD2.[VENDOR CODE], AD2.[SUPPLIER NAME] 
FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM  
#AD AD

所需输出：

供应商代码>发布日期<1><20220304>(2(VEND2，(1(VEND1VEND2<20220611>(2(VEND2，(1(VEND1

零件号		工厂	供应商名称	供应商
123	1001	1	VEND1
123	1001	2

您仍然可以订购日期，只需要一个聚合函数。

我使用了ORDER BY MAX(AD2.[POSTING DATE]) DESC，它应该会带来正确的订单

尽管如此，正如我在评论中所说，更新了一些最近的内容，带来了一些好处，包括STRING_AGG

CREATE TABLE #AD 
(
[PART NUMBER] nvarchar(255), 
[PLANT] nvarchar(255), 
[VENDOR CODE] nvarchar(255), 
[SUPPLIER NAME] nvarchar(255), 
[POSTING DATE] int
)
INSERT INTO #AD 
VALUES ('123', '1001', '1', 'VEND1', 20220304),
('123', '1001', '2', 'VEND2', 20220611)
SELECT 
AD.*,
REPLACE(STUFF((SELECT (', (' + AD2.[VENDOR CODE] + ') ' + AD2.[SUPPLIER NAME])
FROM #AD AD2
WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
AND AD.PLANT = AD2.PLANT 
AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
GROUP BY AD2.[VENDOR CODE], AD2.[SUPPLIER NAME] 
ORDER BY MAX(AD2.[POSTING DATE]) DESC
FOR XML PATH('') ), 1, 2, '')
, '&', '&') AS [Supplier]
FROM  
#AD AD

供应商代码123123

下面的解决方案怎么样？

SQL

DECLARE @tbl TABLE 
(
[PART NUMBER] nvarchar(255), 
[PLANT] nvarchar(255), 
[VENDOR CODE] nvarchar(255), 
[SUPPLIER NAME] nvarchar(255), 
[POSTING DATE] int
);
INSERT INTO @tbl VALUES 
('123', '1001', '1', 'VEND1', 20220304),
('123', '1001', '2', 'VEND2', 20220611);
SELECT * FROM @tbl;
SELECT AD.[PART NUMBER], ad.PLANT
, REPLACE(STUFF((SELECT (', (' + AD2.[VENDOR CODE] + ') ' + AD2.[SUPPLIER NAME])
FROM @tbl AD2
WHERE AD.[PART NUMBER] = AD2.[PART NUMBER] 
AND AD.PLANT = AD2.PLANT 
AND (AD2.[POSTING DATE] BETWEEN 20220101 AND 20221231)-- Is there a way to order this by POSTING DATE DESC within here?
ORDER BY AD2.[POSTING DATE] DESC
FOR XML PATH('')), 1, 2, ''), '&', '&') AS [Supplier]
FROM @tbl AD
GROUP BY AD.[PART NUMBER], ad.PLANT;

输出

零件号		工厂	供应商
123	1001	(2(VEND2，(1(VEND1