如何在TSQL中按ID分组仅显示首行完整数据其余行展示指定字段

Got it, I understand exactly what you're trying to achieve: after running your multi-table INNER JOIN query, you want the first row for each unique ID to display all full field values (ID, The GUID, Quantity, Maint Part Number, Ship Group, Date Received), while any subsequent rows for the same ID should only show the Maint Part Number, Ship Group, and Date Received fields, with the rest left blank.

This is a common formatting requirement for report-style outputs, and we can solve it cleanly using SQL window functions. Here's how to make it work:

Core Approach #

1. Add row numbering per ID: Use the ROW_NUMBER() window function to assign a sequential number to each row within the same ID group. The first row of each group gets a number of 1, subsequent rows get 2, 3, etc.
2. Conditionally show/hide fields: Use CASE WHEN statements to check the row number. For rows where the number is 1, return the original field value; for all other rows, return an empty string (or NULL, depending on your data type needs) for the fields you want to hide.

Full Modified Query #

Here's the adjusted version of your original query that implements this logic:

SELECT 
    -- Show ID only for the first row of each ID group
    CASE WHEN row_num = 1 THEN BL.ID ELSE '' END AS 'ID',
    -- Show The GUID only for the first row
    CASE WHEN row_num = 1 THEN BL.guid ELSE '' END AS 'The GUID',
    -- Show Quantity only for the first row
    CASE WHEN row_num = 1 THEN BL.qty ELSE '' END AS 'Quantity',
    -- Always show these three fields
    I.inv_maintPartNum AS 'Maint Part Number',
    I.inv_ShipGrp AS 'Ship Group',
    I.inv_DateRec AS 'Date Received'
FROM (
    -- Subquery to add row numbering per ID
    SELECT 
        BL.ID,
        BL.guid,
        BL.qty,
        I.inv_maintPartNum,
        I.inv_ShipGrp,
        I.inv_DateRec,
        -- Assign row numbers grouped by ID; adjust ORDER BY to control which row is first
        ROW_NUMBER() OVER (PARTITION BY BL.ID ORDER BY I.inv_DateRec) AS row_num
    FROM BizLine AS BL
    INNER JOIN inventory AS I ON BL.ID = I.ID
    -- Add any additional JOIN/WHERE clauses here as needed
) AS numbered_rows
-- Keep results ordered by ID and row number to maintain group order
ORDER BY ID, row_num;

Key Details to Adjust #

• PARTITION BY BL.ID: This splits your result set into groups where each group has the same ID—this is how we ensure row numbering is per unique ID.
• ORDER BY I.inv_DateRec: This determines which row in each ID group gets the number 1. You can replace I.inv_DateRec with another field (like a creation timestamp or primary key) if you need to prioritize a different row as the "first" one to show full fields.
• Empty string vs NULL: If your fields are numeric (like Quantity), using '' might cause casting issues. In that case, use NULL instead of '' for numeric fields.

Example Output #

If you have an ID with multiple rows (e.g., ID=3 has 3 entries), your output will look like this:

ID |The GUID |Quantity |Maint Part Number |Ship Group |Date Received
-----------------------------------------------------------------------------------------
2 |54219-8974-8702-852-5425 |50 |54VRT |ShipG105 |06/08/2018
3 |68v3f-5kjd-46ee-586-5988 |10 |M6eR5w |ShipG001 |10/19/2010
3 | | |MRE102 |ShipG002 |10/20/2010
3 | | |MRE103 |ShipG003 |10/21/2010
4 |ErR20-bvmd-0001-bGT-0O0O |100 |MRE101 |ShipG99 |01/01/2011

This matches exactly the format you're looking for.

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