我们可以使用LEAD为任何给定的DetailRecordId+ColumnName对拉取下一行，并使用模（%）仅抓取第一、第三、第五行，以此类推。

WITH cte AS
(
  SELECT 
    TableName,
    ColumnName,
    RecordId,
    ImageType,
    ROTId,
    DetailRecordId, 
    NewRecordId = CONCAT(RTRIM(RecordId), '$' 
       + LEAD(RecordId,1) OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)),
    NewCaption = CONCAT(RTRIM(Caption), '$' 
       + LEAD(Caption,1) OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)),
    rn = ROW_NUMBER() OVER (PARTITION BY DetailRecordId, ColumnName 
         ORDER BY ColumnName)
  FROM dbo.YourTableName
)
SELECT 
  TableName,
  ColumnName,
  RecordId = NewRecordId,
  Caption = NewCaption,
  ImageType,
  ROTId,
  DetailRecordId
FROM cte 
WHERE rn % 2 = 1;

工作示例in this fiddle。
重复的OVER()子句有点让我烦恼；在SQL Server 2022中，我们将能够通过新的“sharable”WINDOW子句大大简化这一过程：

WITH cte AS
(
  SELECT 
    TableName,
    ColumnName,
    RecordId,
    ImageType,
    ROTId,
    DetailRecordId, 
    NewRecordId = CONCAT(RTRIM(RecordId), '$' + LEAD(RecordId,1) OVER W),
    NewCaption  = CONCAT(RTRIM(Caption),  '$' + LEAD(Caption,1) OVER W),
    rn = ROW_NUMBER() OVER W
  FROM dbo.YourTableName
  -- magic vvv
  WINDOW W AS ( PARTITION BY DetailRecordId, ColumnName ORDER BY ColumnName) 
  -- magic ^^^
)
SELECT 
  TableName,
  ColumnName,
  RecordId = NewRecordId,
  Caption = NewCaption,
  ImageType,
  ROTId,
  DetailRecordId
FROM cte 
WHERE rn % 2 = 1;

如果您的SQL版本支持STRING_AGG函数，可以尝试以下操作：

SELECT MAX(TableName) TableName, ColumnName,STRING_AGG(D.RecordId, '$') RecordId,
       STRING_AGG(D.Caption, '$') Caption, MAX(ImageType) ImageType, 
       MAX(ROTId) ROTId, MAX(DetailRecordId) DetailRecordId
FROM
(
  SELECT *,
         (COUNT(*) OVER (PARTITION BY ColumnName, DetailRecordId ORDER BY RecordId) + 1) /2  grp
  FROM table_name
) D
GROUP BY ColumnName, DetailRecordId, D.grp
ORDER BY  DetailRecordId, ColumnName

对于其他版本：

WITH CTE AS
  (
   SELECT *,
         (COUNT(*) OVER (PARTITION BY ColumnName, DetailRecordId ORDER BY RecordId) + 1) /2  grp
   FROM table_name
  )

SELECT MAX(TableName) TableName, ColumnName,
       STUFF((SELECT '$' + CAST(T.RecordId as VARCHAR(MAX))
              FROM CTE T WHERE T.ColumnName = D.ColumnName AND
                               T.DetailRecordId = D.DetailRecordId AND
                               T.grp = D.grp 
              FOR xml PATH ('')), 1, 1, '') RecordId,
       STUFF((SELECT '$' + CAST(T.Caption as VARCHAR(MAX))
              FROM CTE T WHERE T.ColumnName = D.ColumnName AND
                               T.DetailRecordId = D.DetailRecordId AND
                               T.grp = D.grp 
              FOR xml PATH ('')), 1, 1, '') Caption,
       MAX(ImageType) ImageType, MAX(ROTId) ROTId, MAX(DetailRecordId) DetailRecordId
FROM CTE D
GROUP BY ColumnName, DetailRecordId, D.grp
ORDER BY  DetailRecordId, ColumnName

请参阅demo。