编号重复的行组-数字组

我有一个表格，其中的一栏显示重复的数字1至7。

EDRImportID FileID  value           colnum      rn
2           752454  PN                  1       1
2           752454  NNN                 2       2
2           752454  NNNN                3       3 
2           752454  01/01/2019 00:00    4       4
2           752454  0                   5       5
2           752454  01/01/2019 00:30    6       6
2           752454  0                   7       7
2           752454  NNN                 1       8
2           752454  NNNN                2       9
2           752454  SHOS-1              3       10
2           752454  01/01/2019 00:30    4       11
2           752454  0                   5       12
2           752454  01/01/2019 01:00    6       13
2           752454  0                   7       14
2           752454  NNN                 1       15
2           752454  NNNN                2       16

所以我想第1行7到GroupNumber 1，第8到14行GroupNumber 2等。

该表来自保存在表中的csv字符串，并将始终具有7行。这是生成表的SQL。

SELECT 
    I.EDRImportID,
    FileID,
    value,
    ROW_NUMBER() OVER(PARTITION BY Record,FileID ORDER BY (SELECT NULL)) as colnum,
    ROW_NUMBER() OVER(PARTITION BY FileID ORDER BY (SELECT NULL)) as rn
FROM Base.Genesis_ImportFile I
    CROSS APPLY STRING_SPLIT(Record, ',') AS Cols
WHERE I.FileType = 'PN'
AND I.Record like 'PN%'
AND I.EDRImportID = 2
AND FileID = 752454

一旦有了组号，就需要将每个组的7行透视为7列。我尝试了下面的代码，但是它使用了MAX聚合，该聚合将仅返回7列的一行，而不是几行

WITH PNColumns AS
(
    SELECT 
        I.EDRImportID,
        FileID,
        value,
        ROW_NUMBER() OVER(PARTITION BY Record,FileID ORDER BY (SELECT NULL)) as rn
    FROM Base.Genesis_ImportFile I
        CROSS APPLY STRING_SPLIT(Record, ',') AS Cols
    WHERE I.FileType = 'PN'
    AND I.Record like 'PN%'
    AND I.EDRImportID = 2
    AND FileID = 752454
)

SELECT 
    EDRImportID,
    FileID,
    [2] AS Station,
    [3] AS Unit,
    [4] AS FromDate,
    [5] AS FromMW,
    [6] AS ToDate,
    [7] AS ToMW
FROM PNColumns 
PIVOT(
    MAX(value)
    FOR rn IN ([2],[3],[4],[5],[6],[7])  
) as PVT