要在此处使用条件聚集：

MAX(CASE CourseCategory WHEN 'Main' THEN ItemName END) OVER (PARTITION BY TransactionID)

假设每个事务可以有很多主餐，那么实际上需要一个LAG函数来忽略某些行。在一些RDMBS中有一个IGNORE NULLS选项，在那里你可以使用如下的内容：

LAG(CASE WHEN CourseCategoruy = 'Main' THEN ItemName END) IGNORE NULLS
    OVER(PARTITION BY TransactionID ORDER BY Linenumber)

不幸的是SQL Server不支持这个，但是Itzik Ben-Gan came up with a nice workaround使用窗口函数。本质上，如果你把你的排序列和数据列组合成一个二进制值，你可以用类似于LAG的方式使用MAX，因为最大二进制值总是前一行，因为排序列是它的一部分。
但是，这确实会让阅读变得非常痛苦，因为您必须将两列转换为二进制并将它们组合起来：

CONVERT(BINARY(4), t.Linenumber) + 
 CONVERT(BINARY(50), CASE WHEN t.CourseCategory = 'Main' THEN t.ItemName END)

那就取最大值

MAX(<result of above expression>) OVER(PARTITION BY t.TransactionID ORDER BY t.LineNumber)

然后删除前4个字符（从linenumber）并转换回varchar：

CONVERT(VARCHAR(50), SUBSTRING(<result of above expression>, 5, 50)

最后，您只需要显示非主餐的此值

CASE WHEN t.CourseCategory <> 'Main' THEN <result of above expression> END

将这些信息结合起来，沿着加上一些示例数据，您最终会得到：

DROP TABLE IF EXISTS #T;
CREATE TABLE #T
(
    RowWithinTxn INT,
    TransactionID INT,
    LineNumber INT,
    CourseCategory VARCHAR(6),
    ItemName VARCHAR(10)
);

INSERT INTO #T
(
    RowWithinTxn,
    TransactionID,
    LineNumber,
    CourseCategory,
    ItemName
)
VALUES
    (1, 123, 123456, 'Main', 'Steak'),
    (2, 123, 123457, 'Choice', 'Chips'),
    (3, 123, 123458, 'Choice', 'Beans'),
    (1, 124, 124567, 'Main', 'Fish'),
    (2, 124, 124568, 'Choice', 'Mushy Peas');

SELECT  t.RowWithinTxn, 
        t.TransactionID,
        t.LineNumber, 
        t.CourseCategory, 
        t.ItemName, 
        AssociatedMainMeal = CASE WHEN t.CourseCategory <> 'Main' THEN 
                                CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Linenumber) + CONVERT(BINARY(50), CASE WHEN t.CourseCategory = 'Main' THEN t.ItemName END))
                                                                    OVER(PARTITION BY t.TransactionID ORDER BY t.LineNumber), 5, 50)) 
                            END
FROM    #T AS t;