按日期将多列合并为单字符串的SQL查询实现

咱们一步步来搞定这个需求：按ItemCode分组，每个商品下要列出不同供应商最新生效日期的单价，并且格式化成指定的字符串形式。

原始价格表数据 #

ItemCode VendorCode UnitCost StartingDate
333      362        2.31     2016-08-19 00:00:00.0
333      362        2.16     2018-02-22 00:00:00.0
444      362        12.96    2014-01-09 00:00:00.0
444      362        13.10    2015-01-09 00:00:00.0
444      430        13.05    2017-04-01 00:00:00.0
444      550        13.30    2018-02-01 00:00:00.0

预期输出 #

333:(362,2.16,2018-02-22)
444:(362,13.10,2015-01-09),(430,13.05,2017-04-01),(550,13.30,2018-02-01)

你的原始SQL问题分析 #

你之前的SQL有两个核心问题：

1. 没有筛选每个(ItemCode, VendorCode)组合下的最新日期记录，导致旧的单价也会被混入；
2. 字符串拼接的格式不符合要求，且没有正确去重分组，最终输出会出现重复内容。

正确的SQL解决方案 #

我们用CTE（公共表表达式）结合窗口函数来实现，分两步处理：

第一步：获取每个供应商对应商品的最新记录

先通过ROW_NUMBER()窗口函数，给每个ItemCode+VendorCode的组合按生效日期倒序排名，排名为1的就是最新记录：

WITH LatestPricelist AS (
    SELECT 
        ItemCode,
        VendorCode,
        UnitCost,
        StartingDate,
        -- 按商品+供应商分组，日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY ItemCode, VendorCode ORDER BY StartingDate DESC) AS rn
    FROM Pricelist
)
SELECT 
    ItemCode,
    VendorCode,
    UnitCost,
    -- 把日期转成YYYY-MM-DD的短格式
    CONVERT(VARCHAR(10), StartingDate, 23) AS StartingDateShort
FROM LatestPricelist
WHERE rn = 1

第二步：拼接成目标格式字符串

基于上面的结果，把每个供应商的信息格式化成指定字符串，再按ItemCode分组拼接：

WITH LatestPricelist AS (
    SELECT 
        ItemCode,
        VendorCode,
        UnitCost,
        StartingDate,
        ROW_NUMBER() OVER (PARTITION BY ItemCode, VendorCode ORDER BY StartingDate DESC) AS rn
    FROM Pricelist
),
FilteredData AS (
    SELECT 
        ItemCode,
        -- 格式化单个供应商的信息为(VendorCode,UnitCost,Date)
        '(' + CAST(VendorCode AS VARCHAR) + ',' + CAST(UnitCost AS VARCHAR) + ',' + CONVERT(VARCHAR(10), StartingDate, 23) + ')' AS VendorInfo
    FROM LatestPricelist
    WHERE rn = 1
)
-- 按ItemCode分组拼接所有供应商信息
SELECT 
    ItemCode + ':' + STUFF(
        (SELECT ',' + VendorInfo 
         FROM FilteredData fd2 
         WHERE fd2.ItemCode = fd1.ItemCode 
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 1, '') AS Result
FROM FilteredData fd1
GROUP BY ItemCode
ORDER BY ItemCode

逻辑说明 #

1. LatestPricelist CTE：给每个商品+供应商的记录做排名，确保只保留最新的价格记录；
2. FilteredData CTE：把每条最新记录转换成你需要的(供应商编码,单价,生效日期)格式字符串；
3. 最后用FOR XML PATH做字符串拼接，STUFF函数去掉拼接后开头多余的逗号，再和ItemCode组合成最终的输出格式。

执行这个SQL就能得到和你预期完全一致的结果啦！

内容的提问来源于stack exchange，提问作者HarryD