SQL Server将两行数据合并为一行的查询语句需求

Got it, let's work through this problem. You've got a read-only SQL Server table where each product has separate rows for Process A and Process B, and you need to combine those into a single row per product with no redundant NULL values—matching that highlighted target output you referenced.

First, Let's Define a Sample Setup #

I'll use a representative table structure and data to demonstrate the solutions (adjust column names to match your actual table):

-- Example table structure (match your actual schema)
CREATE TABLE ProductProcess (
    ProductID INT,
    ProcessType VARCHAR(10), -- Values: 'A' or 'B'
    ProcessParam1 VARCHAR(50),
    ProcessParam2 INT,
    ProcessParam3 DATE
);

-- Sample test data
INSERT INTO ProductProcess VALUES
(1, 'A', 'ParamA1', 100, '2024-01-01'),
(1, 'B', 'ParamB1', 200, '2024-01-02'),
(2, 'A', 'ParamA2', 150, '2024-01-03'),
(2, 'B', 'ParamB2', 250, '2024-01-04');

Method 1: Self-Join (Simple & Straightforward) #

This approach joins the table to itself, linking Process A rows to their corresponding Process B rows for the same product. It eliminates redundant NULLs because each process's parameters map directly to dedicated columns:

SELECT
    pA.ProductID,
    -- Process A parameters
    pA.ProcessParam1 AS ProcessA_Param1,
    pA.ProcessParam2 AS ProcessA_Param2,
    pA.ProcessParam3 AS ProcessA_Param3,
    -- Process B parameters
    pB.ProcessParam1 AS ProcessB_Param1,
    pB.ProcessParam2 AS ProcessB_Param2,
    pB.ProcessParam3 AS ProcessB_Param3
FROM ProductProcess pA
LEFT JOIN ProductProcess pB
    ON pA.ProductID = pB.ProductID
    AND pB.ProcessType = 'B'
WHERE pA.ProcessType = 'A';

Why this works: We use LEFT JOIN so if a product only has Process A data, the Process B columns will show NULL (not redundant—just missing data). If every product has both processes, you could swap to INNER JOIN for slightly better performance.

Method 2: PIVOT with CASE Statements (Flexible for Scaling) #

If you have more process parameters or might add more process types later, this method is easier to extend. We use conditional aggregation to pivot rows into columns:

SELECT
    ProductID,
    -- Extract Process A parameters
    MAX(CASE WHEN ProcessType = 'A' THEN ProcessParam1 END) AS ProcessA_Param1,
    MAX(CASE WHEN ProcessType = 'A' THEN ProcessParam2 END) AS ProcessA_Param2,
    MAX(CASE WHEN ProcessType = 'A' THEN ProcessParam3 END) AS ProcessA_Param3,
    -- Extract Process B parameters
    MAX(CASE WHEN ProcessType = 'B' THEN ProcessParam1 END) AS ProcessB_Param1,
    MAX(CASE WHEN ProcessType = 'B' THEN ProcessParam2 END) AS ProcessB_Param2,
    MAX(CASE WHEN ProcessType = 'B' THEN ProcessParam3 END) AS ProcessB_Param3
FROM ProductProcess
GROUP BY ProductID;

Why this works: The CASE statements isolate values per process type, and MAX (or MIN, since each product-process pair has only one row) pulls the single non-NULL value for each column. Grouping by ProductID ensures one row per product.

Both methods will produce the single-row-per-product output you need, with no redundant NULLs, matching your highlighted target cells. Just adjust the column names to match your actual table's schema!

内容的提问来源于stack exchange，提问作者Tomáš