我向SampleData表中添加了两列：

1. Id：我不知道你表中的顺序
2. RowNum：了解必须向Temp表添加多少列。
   一般来说，您可以使用这个脚本并对其进行改进。

DROP TABLE IF EXISTS #sampleData
CREATE TABLE #sampleData ( [machineId] varchar(18), [periodId] int, [errorId] varchar(13), Id INT IDENTITY, RowNum INT)

INSERT INTO #sampleData ([machineId], [periodId], [errorId])
VALUES
( 'AGR0.37576', 13, 'ERR561' ),
( 'AGR0.37576', 14, 'ERR561' ),
( 'AGR0.37576', 14, 'ERR458' ),
( 'AGR0.37576', 14, 'ERR737' ),
( 'AGR0.37576', 15, 'ERR561' ),
( 'AGR0.37576', 15, 'ERR458' ),
( 'AGR0.37576', 16, 'ERR458' ),
( 'AGR0.37576', 16, 'ERR561' ),
( 'AGR0.37576', 17, 'ERR561' ),
( 'AGR0.37576', 17, 'ERR458' ),
( 'AGR0.37576', 18, 'ERR458' ),
( 'AGR0.37576', 18, 'ERR561' ),
( 'AGR0.37576', 19, 'ERR561' ),
( 'AGR0.37576', 20, 'ERR561' ),
( 'AGR0.37576', 21, 'ERR561' ),
( 'AGR0.37576', 22, 'ERR561' ),
( 'AGR0.37576', 29, 'ERR561' ),
( 'AGR0.37576', 30, 'ERR561' ),
( 'AGR0.37576', 96, 'ERR561' ),
( 'AGR0.37576', 97, 'ERR561' ),
( 'AGR0.37576', 111, 'ERR561' ),
( 'AGR0.37576', 112, 'ERR561' ),
( 'AGR0.37576', 113, 'ERR561' ),
( 'AGR7.00012', 9, 'ERR221' ),
( 'AGR7.00012', 9, 'MIS061' ),
( 'AGR7.00012', 9, 'ERG737' ),
( 'AGR7.00012', 9, 'SER003' ),
( 'ROAD.88887', 13, 'ERR561' )

 ;WITH T1 AS (
                SELECT [machineId], [periodId], [errorId], RowNum,
                                ROW_NUMBER() OVER (PARTITION BY [machineId], [periodId] ORDER BY Id) AS RowNumber
                FROM #sampleData
                                    )
  UPDATE T1 SET RowNum = RowNumber

 CREATE TABLE #Temp (machineId VARCHAR(18), periodId INT)

 
 INSERT INTO #Temp (machineId, periodId)
 SELECT machineId, periodId
 FROM #sampleData
 GROUP BY machineId, periodId


 DECLARE @UpdateStatment AS NVARCHAR(MAX) = ''
 DECLARE @SQLString AS NVARCHAR(MAX) = ''

 SELECT @SQLString = @SQLString + ' ALTER TABLE #Temp ADD ERROR' + CAST(S1.RowNum AS NVARCHAR) + '  VARCHAR(13);', 
        @UpdateStatment = @UpdateStatment + 
        'UPDATE T SET ERROR' + CAST(S1.RowNum AS NVARCHAR) + '=  S.ErrorId 
        FROM #Temp T 
                INNER JOIN #SampleData S ON T.machineId = S.machineId AND T.periodId = S.periodId WHERE S.RowNum = ' + CAST(S1.RowNum AS NVARCHAR) + '; '
 FROM #SampleData S1 INNER JOIN 
           (SELECT [machineId], [periodId] 
            FROM #SampleData 
            GROUP BY [machineId], [periodId] 
            HAVING MAX(RowNum) >= ALL (SELECT RowNum FROM #SampleData) ) AS S2 ON S1.[machineId] = S2.[machineId] AND S1.[periodId]  = S2.[periodId] 
 ORDER BY S1.RowNum

 SELECT @SQLString
 SELECT @UpdateStatment

 EXEC SP_executesql @SQLString
 EXEC SP_executesql @UpdateStatment
 
 SELECT * FROM #Temp

--DROP TABLE #Temp
--DROP TABLE #SampleData

有几种方法可以做到这一点。如果你有有限数量的错误，那么你可以做一些简单的事情，像这样

-- static column version
SELECT * FROM 
    (
    SELECT 
            *
            , ColNum = CONCAT('Col', ROW_NUMBER() OVER(PARTITION BY MachineID, PeriodID ORDER BY ErrorID) )
        FROM #sampleData 
    ) s
    PIVOT 
        (
            MIN(ErrorID) 
            FOR [ColNum] IN (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10)
        ) pvt

我在开头添加了'Col'字符串，以使透视语句更具可读性（否则列名将以数字开头）。
这个statis版本将始终生成相同数量的列，即使它们都是空的。
如果您希望它是完全动态的，那么您必须首先生成一个列名列表，最后的语句几乎与上面的语句相同。

-- dynamic version
-- Get a list of column names required
DECLARE @ColNums varchar(1000) = ''
SELECT  @ColNums = CONCAT(@ColNums, ColNum , ', ')
     FROM (SELECT DISTINCT ColNum FROM (
                SELECT 
                        *
                        , ColNum = CONCAT('Col', ROW_NUMBER() OVER(PARTITION BY MachineID, PeriodID ORDER BY ErrorID) ) --column name per machine/period
                    FROM #sampleData ) c
            ) x

SET @ColNums = LEFT(@ColNums, LEN(@ColNums)-1) -- trim the final comma

-- Generate the final sql
DECLARE @sql varchar(max) = 
'SELECT * FROM 
    (SELECT *, ColNum = CONCAT(''Col'', ROW_NUMBER() OVER(PARTITION BY MachineID, PeriodID ORDER BY ErrorID) )
        FROM #sampleData ) s
    PIVOT 
        (MIN(ErrorID) FOR [ColNum] IN (' + @ColNums + ')) pvt 
    ORDER BY MachineID, PeriodID'

EXEC (@sql)

**注意：**动态版本还格式化了列数，因此数字部分的长度总是3个数字，这假设您不会有超过999个错误列！！如果您不需要这么多填充，那么您可以在format函数中将D3减少到D2。

这将得到以下结果