TSQL查询需求：将同编号的qty系列列合并至首条完整记录

Hey there! I see you want to combine all the qty_* column values from multiple rows into comma-separated strings in the first complete row of each number group, while leaving non-qty columns empty in subsequent rows. Let's adjust your query to get exactly that output.

Solution for SQL Server 2017+ (Using STRING_AGG) #

The cleanest way to do this is with STRING_AGG(), a built-in function for concatenating values across rows. We'll pair it with your existing ROW_NUMBER() logic to keep the first row's non-qty columns intact:

;WITH TheDATA AS (
    SELECT 
        BL.number AS 'number',
        BL.line AS 'line',
        BL.partNumber AS 'partNum',
        BL.phoneNumber AS 'phoneNum',
        L.qtySN AS 'qty_SN',
        I.qtyProperty AS 'qty_Property',
        I.qtyName AS 'qty_Name',
        I.qtyTime AS 'qty_Time',
        ROW_NUMBER() OVER (PARTITION BY BL.number ORDER BY BL.number) AS 'RowNo'
    FROM BList AS BL
    INNER JOIN Location AS L ON BL.ID = L.Route
    INNER JOIN Inventory AS I ON L.ID = I.prodID
)
SELECT 
    number AS 'number',
    IIF(RowNo = 1, CONVERT(varchar, line), '') AS 'line',
    IIF(RowNo = 1, partNum, '') AS 'partNum',
    IIF(RowNo = 1, phoneNum, '') AS 'phoneNum',
    -- Aggregate all qty values for the same number group
    STRING_AGG(qty_SN, ',') OVER (PARTITION BY number) AS 'qty_SN',
    STRING_AGG(qty_Property, ',') OVER (PARTITION BY number) AS 'qty_Property',
    STRING_AGG(qty_Name, ',') OVER (PARTITION BY number) AS 'qty_Name',
    STRING_AGG(qty_Time, ',') OVER (PARTITION BY number) AS 'qty_Time'
FROM TheData
-- Only keep the first row (all rows have the same aggregated qty values)
WHERE RowNo = 1;

Solution for SQL Server 2016 and Earlier (No STRING_AGG) #

If you're on an older SQL Server version that doesn't support STRING_AGG(), use the FOR XML PATH method to concatenate values manually:

;WITH TheDATA AS (
    SELECT 
        BL.number AS 'number',
        BL.line AS 'line',
        BL.partNumber AS 'partNum',
        BL.phoneNumber AS 'phoneNum',
        L.qtySN AS 'qty_SN',
        I.qtyProperty AS 'qty_Property',
        I.qtyName AS 'qty_Name',
        I.qtyTime AS 'qty_Time',
        ROW_NUMBER() OVER (PARTITION BY BL.number ORDER BY BL.number) AS 'RowNo'
    FROM BList AS BL
    INNER JOIN Location AS L ON BL.ID = L.Route
    INNER JOIN Inventory AS I ON L.ID = I.prodID
)
SELECT 
    t1.number AS 'number',
    CONVERT(varchar, t1.line) AS 'line',
    t1.partNum AS 'partNum',
    t1.phoneNum AS 'phoneNum',
    -- Concatenate qty_SN values
    STUFF((SELECT ',' + t2.qty_SN 
           FROM TheDATA t2 
           WHERE t2.number = t1.number 
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS 'qty_SN',
    -- Concatenate qty_Property values
    STUFF((SELECT ',' + t2.qty_Property 
           FROM TheDATA t2 
           WHERE t2.number = t1.number 
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS 'qty_Property',
    -- Concatenate qty_Name values
    STUFF((SELECT ',' + t2.qty_Name 
           FROM TheDATA t2 
           WHERE t2.number = t1.number 
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS 'qty_Name',
    -- Concatenate qty_Time values
    STUFF((SELECT ',' + t2.qty_Time 
           FROM TheDATA t2 
           WHERE t2.number = t1.number 
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS 'qty_Time'
FROM TheDATA t1
WHERE t1.RowNo = 1;

How It Works #

• Both queries group rows by number and concatenate all qty_* values into comma-separated strings.
• The ROW_NUMBER() filter ensures we only display the first row of each group, with full non-qty column values, and the merged qty values in that single row.

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